Forum Discussion

sandy_liew's avatar
sandy_liew
Frequent Visitor
1 year ago
Solved

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



DAX
logic
  • divyed's avatar
    divyed
    1 year ago

    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

7 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

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

  • sandy_liew's avatar
    sandy_liew
    Frequent Visitor
    1 year ago

    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 

  • divyed's avatar
    divyed
    Super User
    1 year ago

    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's avatar
    sandy_liew
    Frequent Visitor
    1 year ago


    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's avatar
      sandy_liew
      Frequent Visitor
      1 year ago

      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's avatar
        divyed
        Super User
        1 year ago

        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