MTD from the Previous Year

[DataVitalizer]

Hi Community,

I have a table with dates and values, below 2 visulas based on the July dataset

I am trying to compare the current month's Sum to the same month from the previous year But there is an exception here, I can not compare the whole July 2019  to July 2020 as long we did not reach the end of the month yet
So in order to calculate the Sum from the same month till the same day from the previous year I created the below measure but I get an empty column:

MTD PY = TOTALMTD(SUM('Table'[Value]),DATEADD('Table'[Date],-1,YEAR))

Thank you in advance.

[v-xicai]

Hi @DataVitalizer   ,

Does that make sense? If so, kindly mark the proper reply as a solution to help others having the similar issue and close the case. If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @DataVitalizer ,

You may create a calendar table first of all, then create relationship with your fact table on the date field.

Calendar= CALEANDARAUTO()

Then you may create measure like DAX below, and put the Calendar[Date] instead of 'Table'[Date] into Table visual.

Current Year MTD= CALCULATE(SUM('Table'[Value]),DATESMTD(Calendar[Date]))



Previous Year same MTD =  CALCULATE(SUM('Table'[Value]),DATESMTD(ENDOFMONTH(DATEADD(Calendar[Date],-12,MONTH))))

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@DataVitalizer , Use Date Table.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last Year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,Year)))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

refer

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

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

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[DataVitalizer]

Hi Community,

Just to clarifiy more the Dax formula I am trying to get

Ex1: Current date: 31/7/2020 => compare it to the same month from the last year

Ex2: Current date: 25/7/2020 => compare it to the same month till the 25th from the last year

Thank you in advance

[Anonymous]

You need to use a calendar table that has year, month week date and day of year(an index number 1-365) 

Then you can use a calculate 

VAR current day of year = lookup(dim_calendar(day of year),dim_calendar[date], sales table[transaction date])

Return

sales = calculate(sum(sales), filter(sales table, sales table[transaction date] <= current day of year ))

I use this all the time for period to date calculations. 

[harshnathani]

Hi @DataVitalizer ,

See if this video help you

https://www.youtube.com/watch?v=zYIxukD2KCM

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)