Hi @Anonymous,
I downloaded the YTD_test pbix sent by you.
The approach I had taken is as under.
1. I changed your Dates table formation as - Dates = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))
This is the best way to create a calendar table based on the minimum and maximum date of the fact tables.
2. Created a column called MonthNumber = Month(Dates[Date])
3. Set the MonthName to sorted by column MonthNumber.
4. Created a MonthSequentialNumber column
MonthSequentialNumber = ('Dates'[Year] - MIN( 'Dates'[Year] )) * 12 + 'Dates'[MonthNumber]
This generates a sequential number for each month and incremented by 1 for every month in the Dates table.
In the sample file this number ranges from 11 to 45.
To go back 1 year just need to subtract 12 from this. Similarly to go back 2 years subtract by 24 and so on.
5. Created a measure called
YTD1YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 1 && Dates
[MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 12) )
6. Created a measure called
YTD2YearB4 = Calculate([Total Sales],Filter(ALL(Dates), Dates[Year] = Max(Dates[Year]) - 2 && Dates
[MonthSequentialNumber] <= Max(Dates[MonthSequentialNumber]) - 24) )
Depending on the Year selected it will report the 1year before and 2 year before total sales.
I have uploaded the file in one drive. and the link is
https://1drv.ms/u/s!ApP3mBZyGaHfzyeQc9QxgrROjsFD
If this works for you please accept this as a solution and also give KUDOS.
Cheers
CheenuSing
Did I answer your question? Mark my post as a solution and also give KUDOS !
Proud to be a Datanaut!
