Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
JajatiDev
Helper I
Helper I

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

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_WeekAge_GroupUnconfirmed_Qty.Total_Unconfirmed_ByWeek%ageByAgeGroup%ageRunningTotalByWeek_AgeGroup(DESC)
01/22/2024[ > 120]14021,0060.67% 
01/22/2024[0 - 5]3,32421,00615.82% 
01/22/2024[11 - 20]1,21921,0065.80% 
01/22/2024[21 - 30]4,17521,00619.88% 
01/22/2024[31 - 60]89921,0064.28% 
01/22/2024[6 - 10]7,76021,00636.94% 
01/22/2024[61 - 90]3,25721,00615.51% 
01/22/2024[91 - 120]23221,0061.10% 
01/29/2024[ > 120]15421,7650.71% 
01/29/2024[0 - 5]4,18521,76519.23% 
01/29/2024[11 - 20]8,43621,76538.76% 
01/29/2024[21 - 30]1,91021,7658.78% 
01/29/2024[31 - 60]2,56521,76511.78% 
01/29/2024[6 - 10]1,04121,7654.78% 
01/29/2024[61 - 90]3,25421,76514.95% 
01/29/2024[91 - 120]22021,7651.01% 
02/05/2024[ > 120]13120,8660.63% 
02/05/2024[0 - 5]3,39420,86616.27% 
02/05/2024[11 - 20]7,77120,86637.24% 
02/05/2024[21 - 30]88320,8664.23% 
02/05/2024[31 - 60]3,84820,86618.44% 
02/05/2024[6 - 10]2,23320,86610.70% 
02/05/2024[61 - 90]2,47020,86611.84% 
02/05/2024[91 - 120]13620,8660.65% 
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.