topic Re: Week-To-Date Cumulative Not Resetting Properly in DAX Commands and Tips

Week-To-Date Cumulative Not Resetting Properly

sandy_liew — Mon, 03 Mar 2025 06:36:56 GMT

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

Re: Week-To-Date Cumulative Not Resetting Properly

bhanu_gautam — Mon, 03 Mar 2025 06:44:26 GMT

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

Re: Week-To-Date Cumulative Not Resetting Properly

sandy_liew — Mon, 03 Mar 2025 06:54:39 GMT

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

Re: Week-To-Date Cumulative Not Resetting Properly

divyed — Mon, 03 Mar 2025 08:01:19 GMT

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

I hope this helps.

Warm Regards,

Neeraj

Re: Week-To-Date Cumulative Not Resetting Properly

sandy_liew — Mon, 03 Mar 2025 08:26:54 GMT

Thank you divyed , it worked perfectly!!

Re: Week-To-Date Cumulative Not Resetting Properly

sandy_liew — Wed, 05 Mar 2025 03:20:38 GMT

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

Re: Week-To-Date Cumulative Not Resetting Properly

sandy_liew — Wed, 05 Mar 2025 03:23:14 GMT

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

Re: Week-To-Date Cumulative Not Resetting Properly

divyed — Wed, 05 Mar 2025 05:26:05 GMT

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