Show on

[Phara]

Hello folks,

I'd like to know how can I display in a graphic the data of the 12 months prior without showing the current month. For an example, we are in August, but I just want to show the 12 months before that are completed, so it would be august 2021 to july 2022.

Thank you for your help!

[Digvijay_Singh]

You can achieve this using various techniques but I prefer creating an indicator in the script like this - 

if(monthend([Month Year]) >= monthend(addmonths(today(1),-12)) and monthend([Month Year]) < monthend(today(1)), 1,0)        as _current_month_rolling13_ind

and then use this indicator in your measures like this -

Sum({<_current_month_rolling13_ind={1}>}Sales)

But if you need to respect the user selections of the month year field then the expression needs to be written in the set expression directly.

Thanks,

[Taoufiq_Zarra]

Hello @Phara 
several proposals,
for example if you want to apply the filter in dimension, you can use:

If(Date >= AddMonths(Today(), -11), Date)

or in measure

=SUM({$<DateField = {'>=$(=AddMonths(YearStart(Today()),-11))<=$(=AddMonths(MonthEnd (Today()),-12))'}>} YOURMEASURE)

[Phara]

Hi Taoufiq,

I tried to add this formula If(Date >= AddMonths(Today(), -11), Date) in my dimension and it shows that there is an error. I just wanted to make sure that it's the right formula, nothing is missing.

Thanks

[Frank_S · Accepted solution]

You have an unneeded comma and a missing 'else' value in your formula

Invalid sample formula syntax

=If(Year < Year(now(), -2), Year)

Valid sample formula syntax

=If(Year < Year(now() -2), Year,'Not Valid Year')

take out extra comment and add the 'else' value

Kind regards...