Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper III
4 years ago
Solved

Help in Cumulative Total

Hi   I am facing issues with Cumulative Total. Below are the DAX I am using for cumulative total. I have below table Week Start Date | Margin | Margin Last Year Week | Difference in Margin  I ha...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi apatwal 
    Here is the updated file https://www.dropbox.com/t/l8yJiCeJNMa9ItgD
    Again you are right. And again I shifted all calculations down to day level in order to obtain correct results dynamically regardless on which level slicing is. Changes are mainly in the last year margin measure. However following you can find the code of all measures and calculated columns
    Day Rank column in the date table:

     

    Day Rank = 
    RANKX ( 
        'Date',
        'Date'[Date],,
        ASC
    )

     

    Then the measures are

     

    Total Margin = SUM ( 'Margin data'[Margin] )

     

     

     

    ShowValueForDates = 
    VAR LastDateWithData =
        CALCULATE ( 
            MAX ( 'Margin data'[Invoice Date] ), 
            REMOVEFILTERS () 
        )
    VAR FirstDateVisible =
        MIN ( 'Date'[Date] )
    VAR Result = 
        FirstDateVisible <= LastDateWithData
    RETURN
        Result
    Margin Same Period Last Year = 
    IF (
        [ShowValueForDates],
        VAR FirstDayRanknPeriod = 
            CALCULATE ( 
                MIN ( 'Date'[Day Rank] ),
                'Date'[DateWithData] = TRUE ()
            )
        VAR LastDayRankInPeriod = 
            CALCULATE ( 
                MAX ( 'Date'[Day Rank] ),
                'Date'[DateWithData] = TRUE ()
            )
        RETURN
            CALCULATE (
                [Total Margin],
                REMOVEFILTERS ( 'Date' ),
                'Date'[Day Rank] >= FirstDayRanknPeriod - 364,
                'Date'[Day Rank] <= LastDayRankInPeriod - 364
            )
    )
    Difference in Margin = 
    VAR CurrentYear =
        MAX ( 'Date'[Year] )
    VAR CurrentYearMargin =
        CALCULATE (
            [Total Margin],
            'Date'[Year] = CurrentYear
        )
    VAR MarginLastYear = 
            [Margin Same Period Last Year]
    RETURN
    IF ( 
        NOT ISBLANK ( MarginLastYear ),
        CurrentYearMargin - MarginLastYear
    Margin Diff Cumulative = 
    VAR LastDayInFilter =
        MAX ( 'Date'[Day Rank] )
    RETURN
    IF ( 
        [ShowValueForDates],
        CALCULATE ( 
            [Difference in Margin],
            REMOVEFILTERS ( 'Date' ),
            'Date'[Day Rank] <= LastDayInFilter
        )
    )