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.
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 ?
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 |
---|---|
105 | |
96 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |