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 Mar...
DAX
DAX Commands and Tips
Help me please
Help required
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