Error Message in Calculation: A function 'MAX' has been used in a True/False expression that is used

[AmberM]

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!

[RobertSlattery]

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...

 

[AmberM]

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")
)

[RobertSlattery · Accepted solution]

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!

[Zubair_Muhammad]

Hi @AmberM

Try replacing MAX with LASTDATE

[AmberM]

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