Week-To-Date Cumulative Not Resetting Properly

[sandy_liew]

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

[divyed · Accepted solution]

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

[sandy_liew]

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

[sandy_liew]

@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

[divyed]

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

[divyed · Accepted solution]

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

[sandy_liew · Accepted solution]

Thank you @divyed , it worked perfectly!!

[sandy_liew]

@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 

[bhanu_gautam]

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