Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@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-Y...
Appreciate your Kudos.
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.
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.
@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-Y...
Appreciate your Kudos.
Hi Amitchandak,
Thank you for sharing 3 exmaples , it helps to understand what DAX means.
All the best,
Paris
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |