How to show past X years by Qtr using DAX

[Baba]

Hello,

Looking some help to achive last X year (at least 5 years) data using DAX?  (Snip below on the format).  Currently I am using one measure for each year and works fine if I am selecting "2024" from the slicer above but as soon as I select any other year numbers change and doesn't show what column hearders are.  I need to show this by Qtr.  Would really appriciate help on this.

Thanks in advance

[AmiraBedh]

First you need to create a table with the distinct values of your Years :

YearsTable = 
VAR UniqueYears = DISTINCT ( SELECTCOLUMNS ( Consolidated_Sales_From_2019, "Year", YEAR ( [Date] ) ) )
RETURN
    UniqueYears

Don't create any relationship between your fact table and the Years table :

Then create your measure for Sales :

Sales = CALCULATE(SUM(Consolidated_Sales_From_2019[Actual Sales]))

Create another measure to use it as a filter on the visual :

Sales Last 5 Years = 
 IF(MAX(Consolidated_Sales_From_2019[Year])>(MAX(YearsTable[Year])-5) && 
 MAX(Consolidated_Sales_From_2019[Year])<=MAX(YearsTable[Year]),1,0)

For the Year slicer, use the YearsTable (you may need to check your Quarter column): 

[Baba]

Hi @AmiraBedh ,

I tried many different ways but wasn't able to succed to send you PBIX file or data on excel or thru table or notepad.  So below I copied the data for one year, so if you copy the data 5/6 times and change the year, that will create a data for few years for you to test.  Thanks in advance! 

2019	Actuals	4/1/2019	1	2	AD	1000	1000	1000L Tote	LT	Q2	Apr	4	0	0	0	0	0	0	0	0	0	0	0	0
2019	Actuals	6/1/2019	1	2	AD	1000	1000	1000L Tote	LT	Q2	Jun	6	0	0	0	0	0	0	0	0	0	0	0	0
2019	Actuals	5/1/2019	1	2	AD	1000	1000	1000L Tote	LT	Q2	May	5	0	0	0	0	0	0	0	0	0	0	0	0
2019	Actuals	8/1/2019	1	2	AD	1000	1000	1000L Tote	LT	Q3	Aug	8	0	0	0	0	0	0	0	0	0	0	0	0

[AmiraBedh · Accepted solution]

Can you share some data ?