Forum Discussion
How to bring previous year/month value.
Hello Team,
How to bring previous year/month value based on current FY filter.
Refer the below snap for reference.
Please help here.
Hi Anonymous ,
You can create calendar table first of all, then create relationship between your data table and calendar table on date field.
Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date])) Or Calendar =CALENDARAUTO()
Secondly, you may create measure to get previous year/month value using DAX below.
Measure1= CALCULATE(SUM(Table1[Sales]),DATEADD('Calendar'[Date],-12,MONTH))Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- kentyler
Solution Sage
Your first step is to use Power Query to reshape your data so that the year/month becomes a row value instead of a column header
Code | Name | Year/Month | Value
If you use a date for year/month then you can use something like the techniques described in this post https://radacad.com/previous-dynamic-period-dax-calculation
It's a step further, but you might think about adding a Calendar Table and creating a relationship to your fact table. That way if you are missing data for a year/month it will still show up in your reports. You can google Calendar Table and find many ways to make one. They are exremely useful in DAX
v-xicaiCommunity Support
Hi Anonymous ,
You can create calendar table first of all, then create relationship between your data table and calendar table on date field.
Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date])) Or Calendar =CALENDARAUTO()
Secondly, you may create measure to get previous year/month value using DAX below.
Measure1= CALCULATE(SUM(Table1[Sales]),DATEADD('Calendar'[Date],-12,MONTH))Best Regards,
Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
