Forum Discussion

JajatiDev's avatar
JajatiDev
Icon for Helper II rankHelper II
2 years ago
Solved

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% 
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

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

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

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