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 have to take Cumulative of Difference in Margin

 

Margin is measure

 

Margin Last Year Week is measure calculated as

Margin Last Year Week =
CALCULATE(
[Margin],
FILTER(
ALL('Date'),
'Date'[Week Rank] = MAX('Date'[Week Rank])-52
)
)
 
Difference in Margin  is a measure which is calculated as
Difference in Margin  =
CALCULATE(
([Margin] - [Margin Last Year Week]),
FILTER('Date','Date'[Year] = 2022)
)
 
Cumulative Total DAX
Margin Diff Cumulative =
CALCULATE(
[Difference in Margin],
FILTER(
ALL('Date'),
'Date'[Date] <= MAX('Date'[Date])
)
)
 

We have neagative values from March as we don't have data from March 2022 (difference is calculated by taking difference of previous year week from current year week in focus.)

 

so Feb cumulative value should be 4997451 + 2398854 = 7396305 ( which is coming as 7494045 which is wrong )

 

I want to display this Cumulative value in column chart and also I want that it should display till Feb without using any invoice filter as we have data from Jan 2021 to Feb 2022 and data is populated on weekly basis so this graph will gradually increases weekly.

Any help will be appreciated.

Thanks!

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

     

     

     

     

     


     

24 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    Hi apatwal 
    Try to use Year-Week number instead of week number

    I know you are filtering only 2022 but I would be more comfortable with slicing by Year-Month instead of Month Name. 

  • Hi:

    I agree with tamerj on using Yr-Week No.

    To get your graph to display correctly you can do your measure like this:

    Measure = IF( ISBLANK([Margin], BLANK(), [Incremental Margin Difference]))

    • apatwal's avatar
      apatwal
      Icon for Helper III rankHelper III

      Hi tamerj1 

       

      I had tried using Month Name which is correct showig only filtered data but my cumulative totals are coming wrong.

       

      I am calculating margin difference week wise and then taking cumulative month wise.

       

      Can you be more specific where to use Year-Week number; I am confused here.

       

      I have given below DAX to calculate Week Rank

      Week Rank =
      RANKX(
      ALL('Date'),
      'Date'[Week Start Date],,
      ASC,
      DENSE
      )
       
      Appreciate your response!
      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion

        Hi apatwal 
        Your measure is

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

        What is the Week Rank column? Is it the week number from 1 - 52 or a unique number that do not recreate for other years

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion

    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

      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

        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.

         

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi  apatwal ,

    Is your problem solved, if not, Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

    For problems with totals in your question, you can create a measure.

    Sum_Total =
    var _table=SUMMARIZE('Date', 'Date' [Month Year],"_value",[Margin Difference Cumlative])
    return
    IF(HASONEVALUE('Date' [Month Year]),[ Margin Difference Cumlative],SUMX( _table,[_value]))

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • apatwal's avatar
      apatwal
      Icon for Helper III rankHelper III

      Hi Anonymous 

      Please find the attached PBI Sample file

      Sample file 

      I had calculated weekly difference of Margin and want cumulative sum Month wise.

      Let me know if you need any futher information.