Forum Discussion
Helper IIError Message in Calculation: A function 'MAX' has been used in a True/False expression that is used
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.
Responsive ResidentYes, this is an error because CALCULATETABLE is expecting a filter and you are passing a single value. You need to create a filter.
Modifying the original example (which I can test)...
WeekPeriod =
VAR d0 = TODAY()
VAR w0 = VALUE(YEAR(d0) & FORMAT(WEEKNUM(d0), "00"))
RETURN
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] = w0) ), 'Date'[Date]),"Period","Last Week") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 1) ), 'Date'[Date]),"Period","Last 2 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 -3) ), 'Date'[Date]),"Period","Last 4 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 11) ), 'Date'[Date]),"Period","Last 12 Weeks") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall")
)
You can control the sort order of your filter by adding a sort key column...
WeekPeriod =
VAR d0 = MAX(FactInternetSales[TransDate])
VAR w0 = VALUE(YEAR(d0) & FORMAT(WEEKNUM(d0), "00"))
RETURN
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] = w0) ), 'Date'[Date]),"Period","Last Week", "sortKey", 0) ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 1) ), 'Date'[Date]),"Period","Last 2 Weeks", "sortKey", 1) ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 -3) ), 'Date'[Date]),"Period","Last 4 Weeks", "sortKey", 2) ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , FILTER('Date',[YearWeek] >= w0 - 11) ), 'Date'[Date]),"Period","Last 12 Weeks", "sortKey", 3) ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall", "sortKey", 4)
)...and selecting the sort order for the Period column
This doesn't handle year boundaries however, so a better strategy is to add a column for the start date of the current week using
VAR d0 = MAX(FactInternetSales[TransDate])
VAR wd0 = d0 - WEEKDAY(d0, 2) + 1
VAR _date = ADDCOLUMNS('Date', "Week Starting", [Date] - WEEKDAY([Date], 2) + 1)
RETURN
UNION (
ADDCOLUMNS( FILTER(_date, [Week Starting] = wd0),"Period","Last Week", "sortKey", 0) ,
ADDCOLUMNS(FILTER(_date, [Week Starting] >= wd0 - 7),"Period","Last 2 Weeks", "sortKey", 1) ,
ADDCOLUMNS( FILTER(_date, [Week Starting] >= wd0 - 14),"Period","Last 4 Weeks", "sortKey", 2) ,
ADDCOLUMNS( FILTER(_date, [Week Starting] >= wd0 - 21),"Period","Last 12 Weeks", "sortKey", 3) ,
ADDCOLUMNS( _date,"Period","Overall", "sortKey", 4)
)The relationships are the same idea as the original...
AmberMI 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") )- RobertSlattery
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.
- AmberM
It figures I was overlooking something so simple! Thank you for all of your assistance! Works perfectly now!