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...
DAX Commands and Tips
Help required
  • 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
    )
)

     

     

     

     

     


     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi apatwal 
Please refer to the sample file with the solution https://www.dropbox.com/t/D5m4mMmNS3CVhZA1
The measures are 

Difference in Margin = 
SUMX ( 
    VALUES ( 'Date'[Month Year] ),
    CALCULATE ( 
        IF (
            NOT ISBLANK ( SUM ( Margin[Margin] ) ),
            CALCULATE (
                SUM ( [Margin] ) - [Margin Last Year Week],
                'Date'[Year] = 2022
            )
        )
    )
)
Margin Diff Cumulative = 
    IF (
        NOT ISBLANK ( [Difference in Margin] ),
        CALCULATE (
            [Difference in Margin],
            REMOVEFILTERS ( 'Date' ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    )
  • apatwal's avatar
    apatwal
    Icon for Helper III rankHelper III
    4 years ago

    Hi tamerj1 

     

    This works perfectly fine for me! Thanks for your help.

    But there is some changes which are needed.

    If you see in below screenshot, to calculate difference in margin, the last value of Feb 28 is being considered to calculate difference rather than addition of all values.

    It should be 195325 - 195462 (total of all Margin Last Year Week for a month)

     

     

    Also, if we do not have Margin values then it should not be considered while calculating difference. Like in below, Difference for March month should be 6995.

     

    below DAX is being used to calculate Margin Last year week.

    Margin Last Year Week =
    CALCULATE(
    [Margin],
    FILTER(
    ALL('Date Table'),
    'Date Table'[Week Rank] = MAX('Date Table'[Week Rank])-52
    )
    )

     

     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      apatwal 
      No it is not working perfectly!  Actually all the numbers were wrong as I did not pay attention the first measure [Margin Last Year Week].
      The following should work

      Margin Last Year Week = 
SUMX (
    VALUES ('Date'[Week Rank] ),
    CALCULATE ( 
        IF (
            NOT ISBLANK ( SUM ( Margin[Margin] ) ),
            CALCULATE (
                SUM ( Margin[Margin] ),
                REMOVEFILTERS ( 'Date' ),
                'Date'[Week Rank] = MAX ( 'Date'[Week Rank] ) - 52
            )
        )
    ) 
)
      Difference in Margin = 
SUMX ( 
    VALUES ( 'Date'[Week Rank] ),
    CALCULATE ( 
        IF (
            NOT ISBLANK ( SUM ( Margin[Margin] ) ),
            CALCULATE (
                SUM ( [Margin] ) - [Margin Last Year Week],
                'Date'[Year] = 2022
            )
        )
    )
)
      Margin Diff Cumulative = 
SUMX (
    VALUES ('Date'[Week Rank] ),
    CALCULATE (
        IF (
            NOT ISBLANK ( [Difference in Margin] ),
            CALCULATE (
                [Difference in Margin],
                REMOVEFILTERS ( 'Date' ),
                'Date'[Date] <= MAX ( 'Date'[Date] )
            )
        )
    )
)

      Still not sure about the blanks issue as my sample data don't have blanks. So please check and let me know. Thank you.

       

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

        Hi tamerj1 

         

        Thanks for your reply!

        In below screenshot you can see that Jan 31 (week start date) is mapped to both Jan and Feb and same for Feb 28 which is mapped to Feb and March.

        This is because Jan 31 which is week start date includes Feb month dates. Is there any possibilty that Jan 31 entire week should be entirely mapped to Jan month and same for Feb 28 and for every month end.

         

        Also, your Cumulative measure does not give correct result it looks it is doing cumulative sum weekly basis but we need cumulative sum on month basis.

        If we change VALUES('Date'[Week Rank]') to VALUES('Date'[Month Year]) that would work.

        Thanks!