Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Masters:
I now have data from Jan 19 to Oct 20, presenting as 9/1/2020.
What if I only want to show the latest 12 month/ moving annual months: Nov 19 to Oct 20 at my line chart
what set expression I should put? or any smart way?
Thanks
Include the following set analysis in your measure expression:
{$<[Calendar Year/Month]={"<=$(=max(Total [Calendar Year/Month]))>$(=AddMonths('$(=max(Total [Calendar Year/Month]))',-12))"}>}
for example, if you are summing Amount, it would be:
sum({$<[Calendar Year/Month]={"<=$(=max(Total [Calendar Year/Month]))>$(=AddMonths('$(=max(Total [Calendar Year/Month]))',-12))"}>} Amount)
the formula check goes "ok" ...but the line chart still stay with all the months rather than 12 months.
I attach here for you to see if the coding is correct.
Try this?
Sum({<Year= ,Month=, [Calendar Year/Month] = {">=$(=AddMonths(Max([Calendar Year/Month])), -12)<=$(=Max([Calendar Year/Month])) "}>} Amount)
hi guys, thanks for your answers. I think I sort of getting the knack from your answers.
I try to play around by following your logic. Now I am stuck with that I can filter into time frame that I want.
like from 20 months into 12 months. but at that 12 months, I CAN NOT select one or multiple months particularly.
but the filter panel does show the selective month. The bar chart just do not change accordingly.
is that because I do not follow a good syntax ?
please advise.
hi,
You can try this,
(Sum({$<Month_Year= {'Nov-19',Dec-19','Jan-20','Feb-20','Mar-20','Apr-20','May-20','Jun-20','July-20','Aug-20','Sep-20','Oct-20'}>}Unitcost)
and also between month set functions are there.
regards,
Ksrinivasan
Thanks Ksrinivasan,
I am now using a variable at my set statement for an automation like this
sum({<[Month]={'>=v_Month)'}>}[Sales]
I know if I use hard code like your way, I can still select particular month.
but any way I can achieve that by using variable ? now it just shows me 12 month but I CAN NOT select any or multiple months...
thanks