Forum Discussion
Helper VGet same month, previous year data in new column
Hi All,
I want to get same month, previous year data in new column by using dax.
1 Jan 2020 Previous column shows 1 Jan 2019 actual number.
Please help!
All the best,
Paris
paris , you can use time intelligence and date table. example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) 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))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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
4 Replies
- amitchandak
Super User
paris , you can use time intelligence and date table. example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date])) 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))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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
parisHi Amitchandak,
Thank you for sharing 3 exmaples , it helps to understand what DAX means.
All the best,
Paris
- v-eachen-msft
Community Support
Hi paris ,
You could create a YM column at first.
YM = YEAR('Table'[Date])*100+MONTH('Table'[Date])Then use EARLIER() function to get results.
Previous = CALCULATE ( FIRSTNONBLANK ( 'Table'[Actual], 1 ), FILTER ( 'Table', 'Table'[YM] = EARLIER ( 'Table'[YM] ) - 100 ) )Here is my test file for your reference.
- Ashish_Mathur
Hi,
Create a Calendar Table and add columns there for Year, Month name and Month number. Sort the Month Name column by the Month number. Create a relationship from the Date column of your Data Table to the Date column of the Calendar Table. To your visual, drag the Year and Month Name columns from the Calendar Table. Creae these measures:
Actuals = SUM(Data[Atual])
Actuals in same period last year = Calculate([Actuals],sameperiodlastyear(Calendar[Date]))
Hope this helps.
