What if Parameter - altering report date error?

[brendan_w]

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!

[Ashish_Mathur · Accepted solution]

Hi,

Try this

=CALCULATE( [Tonne Food Received], FILTER('Monthly Data','Monthly Data'[Date] > TODAY() - 'Days Previous'[Days Previous Value] - 1))

Does this work?

[brendan_w]

Thanks 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().