Forum Discussion
DAX: Cumulative Comparison (Count row from table visual)
zeroblack7 File is attached below my signature, I have also included a custom Date table.
GrowthStatusCount = VAR CurrentDate = MAX ( Dates[Date] ) VAR LastMonday = CurrentDate - WEEKDAY ( CurrentDate, 3 ) VAR Result = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( SUMMARIZE ( fSalesTable, Dates[Date], Dates[Day Name], dCompanyGroup[Company] ), Dates[Date] = LastMonday ), "@Growth Status", [GrowthStatus] ), NOT ISBLANK ( [@Growth Status] ) ) RETURN COUNTROWS ( Result )
Super Userzeroblack7 , For WOW , please refer to my blogs
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
or video : https://www.youtube.com/watch?v=pnAesWxYgJ8
You need to create few columns in the date table :
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
And then you can have measures like
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 8 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-8 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
last two weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])-1
&& 'Date'[Week Rank]>=max('Date'[Week Rank])-3))
Hi, amitchandak Thank you for your reply. Now I'm watching you Video. But I'm looking for something that can count my table after put some measure in there.
