Forum Discussion
Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is used
I am receiving an error message in calculation: "A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed."
How do I fix the following formula so that I can create a time period slicer for Last Week, Last 4 Weeks, Last 12 Weeks? My data is at a week number level, not a daily level.
Sales[Week] = 201804, 201803, 201802, 201801, etc
DatePeriod =
UNION(
ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-1),Sales[Week]),"Period","Last Week"),
ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-2),Sales[Week]),"Period","Last 2 Weeks"),
ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-4),Sales[Week]),"Period","Last 4 Weeks"),
ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales,MAX(Sales[Week])-12),Sales[Week]),"Period","Last 12 Weeks"),
ADDCOLUMNS(SUMMARIZE(CALCULATETABLE(Sales),Sales[Week]),"Period","All Other")
)This is the original calculation that I am trying to modify to rollup at a week number level:
http://analyticsavenue.com/power-bi-timeperiod-slicer-for-last-7-dayslast-30-days/
DatePeriod =
UNION (
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-07+1,today()) ), 'Dates'[Date]),"Period","Last 07 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-14+1,today()) ), 'Dates'[Date]),"Period","Last 14 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-30+1,today()) ), 'Dates'[Date]),"Period","Last 30 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates' , DATESBETWEEN('Dates'[Date],today()-90+1,today()) ), 'Dates'[Date]),"Period","Last 90 Days") ,
ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Dates'), 'Dates'[Date]),"Period","Overall")
)Any assistance is a appreciated!
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.
6 Replies
Unfortunately that didn't work either. This is the error message I received. The data is based on a yyyyww level. Good idea though!
new error message with LASTDATEoriginal error message with MAX
Yes, 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...
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.
