Forum Discussion
Sort Order ignores measure??
I have a simple 'Sales' table below. I created a running total measure to compile the [Goal] amounts each week.
When I have the [Week of] sorted by default to [Week of] my measure works fine and the Goals are combined each week over week. When I sort [Week of] by [Order id] then the measure only sums each week individually in the measure. Any idea why this would be happening?
Goal Running Total = CALCULATE(
sum(Sales[Goal]),
FILTER(
all(Sales[Week Of]),
Sales[Week Of] <= MAX(Sales[Week Of])
)
)
(Sorted by [Week of])
(Sorted by [Order id])
1 Reply
DJSwezey , This will work well when you have separate week table with rank on year week column.
Have a separate date/week table joined back with your table
Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW formatcumm = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))
I think simple formula with date will also work
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
or
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
consider new window function with date or week
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8
Time Intelligence, Part of learning Power BI https://youtu.be/cN8AO3_vmlY?t=27510Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
