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!
What do you see in the preview of the expression editor?
Hi,
Try this :
vCurrentMonth = Date(max([Data Month - Year]), 'MMM-YY')
vPreviousMonth = Date(addmonths(max([Data Month - Year]), -1), 'MMM-YY')
Expression
Count({$<MonthYear ={'$(=$(vCurrentMonth))'}>}[Batch Number])
Count({$<MonthYear = {'$(=$(vPreviousMonth))'}>}[Batch Number])
In the preview the date is read correctly:
Hi,
Thanks, but in this case the result for the previous month is equal to the currently selected month.
Please try the Double Quotes (")
count({<[Data Month - Year]={"$(=addmonths([Data Month - Year],-1))"}>} [Batch Number])
Thanks, but the result is 0
I think the problem is that if for example September is selected the formula for previous month cannot calculate the number of batches for August because the available data are already limited only to September.
Do you mean that August data are not part of your dataset?
Or do you have other selections on other fields which prevents August data to be visible?
No, August data are available and there are no other selections apart from the one value of [Data Month - Year] field selected. But somehow if I select 01/09/23 the number of batches for August (01/08/23) are not getting calculated. At the same time the formula Addmonths([Data Month - Year],-1) shows 01/08/23 when 01/09/23 is selected
This is really weird...
If you try with Addmonths([Data Month - Year],0) is it showing September data?