Rolling month

[KKTOES]

Hi everyone,

I would like to create a graph with only the last 3 months of my data instead of all the year,

I wrote :

If([NC.Creation_date.autoCalendar.YearMonth]>= AddMonths(max([NC.Creation_date.autoCalendar.YearMonth]),-3),[NC.Creation_date.autoCalendar.YearMonth])

But it 's not working :'(

Any Idea ?

Thank you very much

[dplr-rn · Accepted solution]

try this.

create a variable called vMonthyearMinus3  as =addmonths(max(MonthYear),-3)

which mean this is calculated 1 time for the entire dashboard.

and change your dimension as if(MonthYear>=vMonthyearMinus3, MonthYear).

Standard/Better way - Ideally i would as you to do this in set analysis rather than as a if statement in dimension. try something like this too. this would be better for performance

Sum({<MonthYear={">=$(=Date(addmonths(max(MonthYear),-3)),'MM/DD/YYYY')"}>}LineSalesAmount)

[KKTOES · Accepted solution]

Why is that better to use set analysis ? because with the "if" statement its appear way more simple than the set analysis

[dplr-rn · Accepted solution]

performance is always better in set analysis.

the if statement will be evaluated for every line which can appear in your table. e.g. if your datamodel has a million rows with date each line will be checked to see if the condition is met.

But set analysis is evaluated for the whole table. the set which satisfies the condtions of the set analysis is chosen .

I know it looks complicated. but it is very powerful

[KKTOES]

Thank you very much for your teaching. In the future i will improve in set analysis. I will try your response.

[KKTOES]

It works thank you really much 

[dplr-rn]

Great to hear.

Mark the solution as answered and close the thread