Forum Discussion

apatwal's avatar
apatwal
Icon for Helper III rankHelper 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
apatwal's avatar
apatwal
Icon for Helper III rankHelper 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
Icon for Super User rankSuper 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