Reply
sandy_liew
Frequent Visitor

Week-To-Date Cumulative Not Resetting Properly

Hi, I have the following DAX logic in order to calculate the cumulative totals by week and reset in the following week, however I observed that the data could cumulate accurately, just that it is not being able to reset every week, for example on 202441, the data was supposed to start back with 515,800 rather than 4,124,687, could anyone here pls help me with this issue?

WTD_DRR_T2_Overall =
CALCULATE(
    [Total_DRR],  
    FILTER(
        ALLSELECTED('F-Spec Output'),  
        WEEKNUM('F-Spec Output'[Finance Date]) = WEEKNUM(MAX('F-Spec Output'[Finance Date],-1)) &&
        YEAR('F-Spec Output'[Finance Date]) = YEAR(MAX('F-Spec Output'[Finance Date])) &&
        'F-Spec Output'[Finance Date] <= MAX('F-Spec Output'[Finance Date])
    )
)

whereby 
[Total_DRR] = CALCULATE(SUM(T2_DRR_Calendar[DRR]), T2_DRR_Calendar[Factory] IN {"ATKL","KTM"})

Screenshot 2025-03-03 143445.png

2 ACCEPTED SOLUTIONS
divyed
Super User
Super User

Hello @sandy_liew ,

 

You can use windows function with Partition By clause to get this result. Here is the dax for your reference.

Week_Cumm =
CALCULATE (
SUM ( Test_Table[Total_DRR] ),
WINDOW (
0,
ABS,
0,
REL,
ALL ( Test_Table[Finance_Date], Test_Table[Week_1] ),
ORDERBY ( Test_Table[Finance_Date] ),
,
PARTITIONBY ( Test_Table[Week_1] )
)
)

 

divyed_0-1740988851992.png

 

I hope this helps.

 

Warm Regards,

Neeraj

LinkedIn : https://www.linkedin.com/in/neeraj-kumar-62246b26/

View solution in original post

Thank you @divyed , it worked perfectly!!

View solution in original post

7 REPLIES 7
sandy_liew
Frequent Visitor

sandy_liew_0-1741144684159.png


Hi, I currently have another DAX logic formula as shown below for Month-to-Date (MTD) cumulative, however it is facing the issue of not following the 202410 month as shown in the "Month" column, supposedly it should not reset on 10/1/2024 but rather it should still continue the cumulation until the "Month" column turn into 202411 in order to reset the MTD calculation

@divyed I tried using your DAX logic formula and change it to partition according to month for Month-To-Date (MTD) cumulative, however it shows the same result as the one you showed me for Week-To-Date (WTD) cumulative, but when I removed the "WW" column, everything will be normal, the thing is it is compulsory for me to show the "WW" column

Hello @sandy_liew ,

 

Could you please share your dax to check further ? 

Please note couple of things in the formula shared:

1. ALL ( Test_Table[Finance_Date], Test_Table[Week_1] ) 

    you need to add fields here , you should use of relaive fields used in dax either in partition by 

    or order by.

2. PARTITIONBY ( Test_Table[Week_1] )

    you can pass multiple fields in correct sequence to get desired result.

 

I hope this helps.

 

Warm Regards,

Neeraj

LinkedIn : https://www.linkedin.com/in/neeraj-kumar-62246b26/
divyed
Super User
Super User

Hello @sandy_liew ,

 

You can use windows function with Partition By clause to get this result. Here is the dax for your reference.

Week_Cumm =
CALCULATE (
SUM ( Test_Table[Total_DRR] ),
WINDOW (
0,
ABS,
0,
REL,
ALL ( Test_Table[Finance_Date], Test_Table[Week_1] ),
ORDERBY ( Test_Table[Finance_Date] ),
,
PARTITIONBY ( Test_Table[Week_1] )
)
)

 

divyed_0-1740988851992.png

 

I hope this helps.

 

Warm Regards,

Neeraj

LinkedIn : https://www.linkedin.com/in/neeraj-kumar-62246b26/

Thank you @divyed , it worked perfectly!!

sandy_liew
Frequent Visitor

@bhanu_gautam Hi, I got this as my result after implementing your code, is it possible to modify the code so that it could start cumulating on 9/28/2024 rather than 9/29/2024, need to follow the Week.1 

Screenshot 2025-03-03 145413.png

bhanu_gautam
Super User
Super User

@sandy_liew , Try using

 

dax
WTD_DRR_T2_Overall =
VAR CurrentWeek = WEEKNUM(MAX('F-Spec Output'[Finance Date]))
VAR CurrentYear = YEAR(MAX('F-Spec Output'[Finance Date]))
RETURN
CALCULATE(
[Total_DRR],
FILTER(
ALLSELECTED('F-Spec Output'),
WEEKNUM('F-Spec Output'[Finance Date]) = CurrentWeek &&
YEAR('F-Spec Output'[Finance Date]) = CurrentYear &&
'F-Spec Output'[Finance Date] <= MAX('F-Spec Output'[Finance Date])
)
)




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)