Forum Discussion

Tarek78's avatar
Tarek78
Helper I
3 years ago

Year-Over-Year calculation

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.

 

DAX
Last Year
year-over-year
YoY calculation

4 Replies

  • FreemanZ's avatar
    FreemanZ
    Super User
    3 years ago

    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]))
  • Tarek78's avatar
    Tarek78
    Helper I
    3 years ago

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

     

  • Tarek78's avatar
    Tarek78
    Helper I
    3 years ago

    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's avatar
      FreemanZ
      Super User
      3 years ago

      hi Tarek78 

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