Forum Discussion

apatwal's avatar
apatwal
Helper III
4 years ago

Difference between cumulative values

Hi,

 

I need help in creating DAX.

I have an requirement to work on Incremental Margin which is cumulative of Margin and want to plot difference of incremental margin.

I need that Incremental Margin for 2022 should subtract the 2021 Incremental Margin from the comparable week. E.g., For week of Jan 3, 2022, we need to calculate the week’s total incremental margin and then subtract the incremental margin from week of Jan 4, 2021.

 

below is the DAX which is used to create measure to calculate cumulative of Incremental Margin

Incremental Margin Cumulative =
CALCULATE(
    SUM('table'[Incremental Margin]),
    FILTER(
        CALCULATETABLE(
            SUMMARIZE(
                'table',
                'table'[YrMnthCode],
                'table'[YearMonth]
            ),
            ALLSELECTED('table')
        ),
        ISONORAFTER(
            'table'[YrMnthCode], MAX('table'[YrMnthCode]), DESC,
            'table'[YearMonth], MAX('table'[YearMonth]), DESC
        )
    )
)
 
Let me know how can I build DAX for this.
DAX
DAX Commands and Tips
Help me please
Help required

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    apatwal , You need have column like these in you date tbale, along with year and week

     

    new columns
    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
    Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
    OR
    Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

     

     

    Then you can have week measures like

    This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
    Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
     

    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

    or

    Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -53)))

     

    or

     

    This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
    Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))

    • apatwal's avatar
      apatwal
      Helper III
      4 years ago

      Hi amitchandak 

       

      Thanks for your reply!

      Just to inform we don't have separate date table, date column is included in our dataset and we have millions of records in our dataset.

       

      • amitchandak's avatar
        amitchandak
        Super User
        4 years ago

        apatwal ,  Try like

        example

        week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Sales'[Date],-364,DAY))

         

        or

        This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Year]=max('Table'[Year]) && 'Table'[Week] = Max('Table'[Week]) ))
        Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Table'),'Table'[Year]=max('Table'[Year])-1 && 'Table'[Week] = Max('Table'[Week])))

         

         

        But My advice would be to get a date table added to the model. Things would be easy