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 no...
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

  • sandy_liew's avatar
    sandy_liew
    1 year ago

    Thank you divyed , it worked perfectly!!

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