Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to Solution.
Can you share some data ?
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):
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 |
Can you share some data ?