Forum Discussion
Advocate IWhat if Parameter - altering report date error?
Hi,
So I have the following DAX which works perfectly:
Daily Tonnage = CALCULATE( [Tonne Food Received], 'Monthly Data'[Date] > TODAY() - 1)
This calculates the tonnage received on today's date. I now set up a what if parameter called Days Previous. This will hopefully allow me to change the day the formula acts on interactively like so:
Daily Tonnage = CALCULATE( [Tonne Food Received], 'Monthly Data'[Date] > TODAY() - 'Days Previous'[Days Previous Value] - 1)
however this gives the error :
Error Message:
Something's wrong with one or more fields: (KPI Measures) Todays Donation: A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.,
TODAY() - 1 and TODAY() - 'Days Previous'[Days Previous Value] - 1 are both scalers, so not sure why this error is occuring. Could anyone provide any help on a potential workaround or shed light on an error on my side please?
Many thanks in advance!
Hi,
Try this
=CALCULATE( [Tonne Food Received], FILTER('Monthly Data','Monthly Data'[Date] > TODAY() - 'Days Previous'[Days Previous Value] - 1))
Does this work?
3 Replies
- Ashish_Mathur
Super User
Hi,
Try this
=CALCULATE( [Tonne Food Received], FILTER('Monthly Data','Monthly Data'[Date] > TODAY() - 'Days Previous'[Days Previous Value] - 1))
Does this work?
brendan_wThanks Ashish, that did the job! Out of curiosity, what is different between the Filter function and the filter argument within Calculate? I've a vague recollection of reading somewhere that they result in the same functionality. Clearly this is not the case or else the code for Calculate has yet to be updated.
- Ashish_Mathur
Hi,
You are welcome. The reason we need to use the FILTER() function is that yours is a case of rich filtering i.e. comparing a column to a measure. The measure being TODAY().