Forum Discussion
Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is used
- 8 years ago
Ah, OK. It's a calculated table, not a measure. You enter the formula in the status bar when creating a table...
Then you use the Period field in your filter...
And set up the relationship to the calculated table...
This was your problem all along I think. Your original formula is actualy fine if you do it like this.
I really, really appreciate the time you are taking to assist me!
Now I get a different error message once I modify your calculation. I have included a link to the practice database I am working with. All of this is based on a custom fiscal calendar year that begins, for example 01/28/16 - 01/26/17 (FY 2017).
This is why I am struggling to use the DAX Date/Time and Time Intelligence functions.
Sample data set:
https://1drv.ms/u/s!AoY5sA-v6cUciEOYS-NvMrcINYu1
New calculation error:
Here is the calculation I used:
WeekPeriod =
Var w0 = MAX(Sales[Week])
RETURN
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] = w0) ), Sales[Week]),"Period","Last Week") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 - 1) ), 'Sales'[Week]),"Period","Last 2 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 -3) ), 'Sales'[Week]),"Period","Last 4 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales' , FILTER('Sales',Sales[Week] >= w0 - 11) ), 'Sales'[Week]),"Period","Last 12 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Sales'), Sales[Week]),"Period","Overall")
)
Ah, OK. It's a calculated table, not a measure. You enter the formula in the status bar when creating a table...
Then you use the Period field in your filter...
And set up the relationship to the calculated table...
This was your problem all along I think. Your original formula is actualy fine if you do it like this.
- AmberM8 years ago
Helper II
It figures I was overlooking something so simple! Thank you for all of your assistance! Works perfectly now!