Year-Over-Year calculation

[Tarek78]

Hi Everyone,

I am completely new to Power Pivot and DAX calculations. I have a simple data model with a date table and a fact table with dates and sales generated per date.

Each month, I am generating a report that shows MTD and YTD sales vs Last year for the same period.

Is there a DAX formula to show this.

[Tarek78]

Thanks! I tried them works fine. 

I also used TotalMTD/TotalYTD and it works perfectly also. 

what's the difference between them? Which one using less resources and size?

[FreemanZ]

hi @Tarek78 

TOTALMTD is simplified form of CALCULATE([], DATESMTD). The later provides more flexibility when we have fiscal years starting not on Jan 1st. 

[Tarek78]

Thanks !!! I will try this next time and keep you posted. 

[FreemanZ]

hi @Tarek78 

you would need some measures with Time Intelligence Functions, like

MTD =

CALCULATE(

SUM(FactTable[Sales]), DATESMTD('calendar'[Date]))

LY MTD =

CALCULATE

([MTD], SAMEPERIODLASTYEAR('calendar'[Date]))

YTD =

CALCULATE(

SUM(FactTable[Sales]), DATESYTD('calendar'[Date]))

LY YTD =

CALCULATE

([YTD], SAMEPERIODLASTYEAR('calendar'[Date]))