Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JCK2
Helper III
Helper III

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! 

 

Last Year same time.PNG

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@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.

View solution in original post

v-diye-msft
Community Support
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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

2 REPLIES 2
v-diye-msft
Community Support
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.

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.