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.
Hi Everyone,
I have a report that has button filters for years. I have a gauge visual grouping with one gauge showing the current year selected and the second gauge showing the previous year to the year selected. Below is the current dax measures for the gauge visuals now. Right now I have single select on, but if the user holds ctrl and selects multiple years, then the visuals show errors. I have been trying to think of a way where 2 years could be selected and the year prior to the Minimum selected would show in the 2nd gauge. I tried different things but haven't found a solutions yet. I wanted to pose the question to everyone here and see what ideas people may have. Any ideas or help would be appreciated.
Year Selected =
VAR StartDate = DATE(ALLSELECTED('Date'[Year]), 1, 1)
VAR EndDate = DATE(ALLSELECTED('Date'[Year]), 12, 31)
VAR Result = CALCULATE(
DIVIDE([Transplant Tissue Totals], [Transplant Rate Denominator]),
DATESBETWEEN('Date'[Date], StartDate, EndDate)
)
Return Result
Previous Year of Selected =
VAR StartDate = DATE(ALLSELECTED('Date'[Year])-1, 1, 1)
VAR EndDate = DATE(ALLSELECTED('Date'[Year])-1, 12, 31)
VAR Result = CALCULATE(
DIVIDE([Transplant Tissue Totals], [Transplant Rate Denominator]),
DATESBETWEEN('Date'[Date], StartDate, EndDate)
)
Return Result
Solved! Go to Solution.
@dbrandone You can change your date to use MIN:
VAR StartDate = DATE(MIN('Date'[Year])-1, 1, 1)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@dbrandone You can change your date to use MIN:
VAR StartDate = DATE(MIN('Date'[Year])-1, 1, 1)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks @AllisonKennedy
That works for the previous year measure, but with the first measure if ALLSELECTED is still used, it throws an error. If I use MAX, then only the max year of the two selected years shows in the data, but I was able to use your premise with MIN and MAX to get the currently selected year visual to display properly. Thanks!!
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |