Hi All ,
I have a date column in my table and dates are all month end dates. That is one date for each month and it is month end.
Example : 30 JUNE 15,31 JULY 15,31 AUG 15,
Now i want to filter the report based on latest month in the report and Quarter start for that max month
i.e here in above scenario report should be filtered date column between 1 JUL 15(QUARTER START FOR MAX DATE IN TABLE ) AND 31 AUG 15(MAX DATE)
Please suggest report expression for the same.Many thanks in advance!
Hi - is this a relational model, or dimensional?
Hi Chris ,
It is relational.
It's difficult to answer accurately without knowing the structure of your data etc. but you'll want something along the lines of:
maximum(
.[DateField])
To find the start date of the quarter you're going to have to know how many months to go back. I haven't tested this (this is all just from the top of my head, so please check it thoroughly), but I'd be looking to try something like:
_first_of_month(_add_months(maximum(.[DateField]),
Case
when month(maximum(.[DateField]) in (1,4,7,10) then 0
when month(maximum(.[DateField]) in (2,5,8,11) then -1
when month(maximum(.[DateField]) in (3,6,9,12) then -2
End)) |
|
|
|
|
Hi Chris,
Thanks for replying . I will surely try this.
Meanwhile i got through this solution :-
1) I created data item which will calculate max date in date field.
[Max View Date] :-maximum(date_column for report) as
2) then i am using below calculation to get to quarter first date.
_first_of_month(_add_months([Max View Date],(((intdiv(extract(month,[Max View Date])-1,3) * 3) + 1)-extract(month,[Max View Date]))))
and then i am using above two items in date range filter.