Forum Discussion

JCK2's avatar
JCK2
Helper III
5 years ago
Solved

Last Year Same Period

I am trying to build a caluclation, which shows me MTD till last year same period.

 

I want to see the numbers deom Jan 2019 till August 2019?

 

I wrote multiple calculation, but not yeilding the desierd result.

 

Any helps is appreciated! 

 

  • @JCK2 , With a date table and time intelligence

    Ventas MTD: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    Last year MTD Sales ? CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
    Mes detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
    Año detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Last MTD Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    Ventas del último mes: CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    Last MTD (completar) Ventas : CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    valor del mes anterior: CALCULATE(sum(''Table''[valor total de horas]),previousmonth('Date'[Date]))

    diff - [Ventas MTD]-[las latest ventas MTD]
    diff % a divide([MTD Sales]-[last MTD Sales],[last MTD Sales])


    The same year last year value of the same month : CALCULATE(sum(''Table''[total value of hours]), previousmonth(dateadd('Date'[Date],-11,MONTH)))

    Power BI — MTD
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

    To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. Recommended:
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi


    We appreciate your congratulations.

  • Hello @JCK2

    Try something like this:

    Revenue MTD PY = 
    VAR __today = TODAY()
    VAR __lastYear = EDATE ( __today, -12 )
    RETURN
    CALCULATE (
        [Revenue MTD],
        SAMEPERIODLASTYEAR ( 'Dim Calendar'[Date] ),
        'Dim Calendar'[Date] <= __lastYear
    )

    O

    MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date])) 

    If it doesn't help, you might consider providing your fictitious pbix that would be helpful for us to investigate further.

2 Replies

  • @JCK2 , With a date table and time intelligence

    Ventas MTD: CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    Last year MTD Sales ? CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
    last year MTD (complete) Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
    Mes detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))
    Año detrás de Ventas: CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
    Last MTD Sales - CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
    Ventas del último mes: CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    Last MTD (completar) Ventas : CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
    valor del mes anterior: CALCULATE(sum(''Table''[valor total de horas]),previousmonth('Date'[Date]))

    diff - [Ventas MTD]-[las latest ventas MTD]
    diff % a divide([MTD Sales]-[last MTD Sales],[last MTD Sales])


    The same year last year value of the same month : CALCULATE(sum(''Table''[total value of hours]), previousmonth(dateadd('Date'[Date],-11,MONTH)))

    Power BI — MTD
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e

    To get the best out of the time intelligence function. Make sure that you have a date calendar and that it has been marked as the date in the model view. Also, join her with the date column of your events. Recommended:
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi


    We appreciate your congratulations.

  • v-diye-msft's avatar
    v-diye-msft
    Community Support

    Hello @JCK2

    Try something like this:

    Revenue MTD PY = 
    VAR __today = TODAY()
    VAR __lastYear = EDATE ( __today, -12 )
    RETURN
    CALCULATE (
        [Revenue MTD],
        SAMEPERIODLASTYEAR ( 'Dim Calendar'[Date] ),
        'Dim Calendar'[Date] <= __lastYear
    )

    O

    MTD LY = CALCULATE(TOTALMTD(SUM('Calendar'[Amount]),'Calendar'[Date]),SAMEPERIODLASTYEAR('Calendar'[Date])) 

    If it doesn't help, you might consider providing your fictitious pbix that would be helpful for us to investigate further.