Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Why does this work...
Date = VAR d0 = MAX(FactInternetSales[TransDate]) RETURN ADDCOLUMNS(CALENDAR("2013-Jan-01",d0), "Week Starting", [Date] - WEEKDAY([Date], 2) + 1) WeekPeriod = VAR d0 = MAX(FactInternetSales[TransDate]) VAR wd0 = d0 - WEEKDAY(d0, 2) + 1 RETURN UNION ( ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Date' , FILTER('Date', [Week Starting] = wd0) ), 'Date'[Date]),"Period","Last Week", "sortKey", 0) , ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Date' , FILTER('Date', [Week Starting] >= wd0 - 7) ), 'Date'[Date]),"Period","Last 2 Weeks", "sortKey", 1) , ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Date' , FILTER('Date', [Week Starting] >= wd0 - 14) ), 'Date'[Date]),"Period","Last 4 Weeks", "sortKey", 2) , ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Date' , FILTER('Date', [Week Starting] >= wd0 - 21) ), 'Date'[Date]),"Period","Last 12 Weeks", "sortKey", 3) , ADDCOLUMNS( SUMMARIZE(CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall", "sortKey", 4) )
and this doesn't?
Date = VAR d0 = MAX(FactInternetSales[TransDate]) RETURN CALENDAR("2013-Jan-01",d0) WeekPeriod = 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( CALCULATETABLE(_date , FILTER(_date, [Week Starting] = wd0) ),"Period","Last Week", "sortKey", 0) , ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 7) ),"Period","Last 2 Weeks", "sortKey", 1) , ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 14) ),"Period","Last 4 Weeks", "sortKey", 2) , ADDCOLUMNS( CALCULATETABLE(_date , FILTER(_date, [Week Starting] >= wd0 - 21) ),"Period","Last 12 Weeks", "sortKey", 3) , ADDCOLUMNS( CALCULATETABLE(_date),"Period","Overall", "sortKey", 4) )
You can try it by modifying this example
Hi @RobertSlattery,
In your scenario, what did you mean that your second formula did not work? Did you get any error message? Or you mean that the result you got is not what you want? Then what is your desired result?
I have made some test in your shared file. It seems like the expression works for me.
Please refer to Page 2: https://1drv.ms/u/s!AlqSnZZUVHmshBcrpJQmKwSHQRpo
Thanks,
Xi Jin.
Hi @v-xjiin-msft, if you look closely at your example you will see that the filter is not filtering. All dates are in all categories. This is the same problem I have (although I worked arround it).
The desired result is...
If you look closely at the other table, you will not see this transition.
These filters are not filtering at all...
The result of the boolean expressions is always true.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |