Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Community,
Hopefully you will be able to help me with this question. I need to calculate a measure which corresponds to the previous month from the one that is currently selected in the filter (for example if September is selected the measure should be calculated for August). I am trying to use following formula:
count({<[Data Month - Year]={'$(=addmonths([Data Month - Year],-1))'}>} [Batch Number])
Field [Data Month - Year] has date format as for example 01/09/23. If I use it without set expression just to show the date it works correctly: if 01/09/23 is selected the formula Addmonths([Data Month - Year],-1) shows 01/08/23
But somehow the formula to count batches shows 0:
count({<[Data Month - Year]={'$(=addmonths([Data Month - Year],-1))'}>} [Batch Number])
If I try to add = sign before $ it calculates total number of batches for September (currently selected month):
count({<[Data Month - Year]={'=$(=addmonths([Data Month - Year],-1))'}>} [Batch Number])
If I try to add currently selected month to be ignored the result is still 0:
count({<[Data Month - Year],[Data Month - Year]={'$(=addmonths([Data Month - Year],-1))'}>} [Batch Number])
Any help would be highly appreciated! Thanks a lot in advance!
Yes, then it shows September
And if you create a table with [Data Month - Year] as a dimension and count([Batch Number]) as a measure, without any active selection? Does it returns Aug?
Dear all,
I have realized that the filter had an expression set for the date selection:
=Dual(Year([Data Month - Year])&'-'&Month([Data Month - Year]),MonthStart([Data Month - Year]))
Once I have changed it to [Data Month - Year.autoCalendar.YearMonth] and also reflected it in the formula for the prev. month it started working. Thanks a lot to all for your advice!