Cumulative Total By Week & Running Total By Week Age Group (DESC)

[JajatiDev]

Hi,

I'm working on developing a trend chart. however, the below functions are not providing the desired results. Shared below are the functions I have used.

Total_Unconfirmed_ByWeek = CALCULATE(SUM('Table'[Unconfirmed_Qty.]),FILTER(ALL('Table'),'Table'[Report_Week] <= MAX('Table'[Report_Week])))

Total_Unconfirmed_ByWeek = CALCULATE(SUM('Table (2)'[Unconfirmed_Qty.]),FILTER(ALL('Table (2)'[Report_Week]),'Table (2)'[Report_Week] <= MAX('Table (2)'[Report_Week])))

The columns marked in bold are where I need your assistance.

1. data captured in columns Total_Unconfirmed_ByWeek and %ageByAgeGroup is what I expect

2. %ageRunningTotalByWeek_AgeGroup(DESC) is where I need assistance with the function

Report_Week	Age_Group	Unconfirmed_Qty.	Total_Unconfirmed_ByWeek	%ageByAgeGroup	%ageRunningTotalByWeek_AgeGroup(DESC)
01/22/2024	[ > 120]	140	21,006	0.67%
01/22/2024	[0 - 5]	3,324	21,006	15.82%
01/22/2024	[11 - 20]	1,219	21,006	5.80%
01/22/2024	[21 - 30]	4,175	21,006	19.88%
01/22/2024	[31 - 60]	899	21,006	4.28%
01/22/2024	[6 - 10]	7,760	21,006	36.94%
01/22/2024	[61 - 90]	3,257	21,006	15.51%
01/22/2024	[91 - 120]	232	21,006	1.10%
01/29/2024	[ > 120]	154	21,765	0.71%
01/29/2024	[0 - 5]	4,185	21,765	19.23%
01/29/2024	[11 - 20]	8,436	21,765	38.76%
01/29/2024	[21 - 30]	1,910	21,765	8.78%
01/29/2024	[31 - 60]	2,565	21,765	11.78%
01/29/2024	[6 - 10]	1,041	21,765	4.78%
01/29/2024	[61 - 90]	3,254	21,765	14.95%
01/29/2024	[91 - 120]	220	21,765	1.01%
02/05/2024	[ > 120]	131	20,866	0.63%
02/05/2024	[0 - 5]	3,394	20,866	16.27%
02/05/2024	[11 - 20]	7,771	20,866	37.24%
02/05/2024	[21 - 30]	883	20,866	4.23%
02/05/2024	[31 - 60]	3,848	20,866	18.44%
02/05/2024	[6 - 10]	2,233	20,866	10.70%
02/05/2024	[61 - 90]	2,470	20,866	11.84%
02/05/2024	[91 - 120]	136	20,866	0.65%

[amitchandak · Accepted solution]

@JajatiDev , Create a week or date table and join it back with your date of the 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 format

Cumm Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))

But I think simple cumulative should also work in case date table view by week

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))

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