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,
I'm using a gauge to display selected stats (be it year, month or week day) vs the average of the same date range from the previous years and current year inclusive:
I.e. if October 2020 is selected, the red highlighted part will show the stats for October 2020 while the yellow highlighted parts will show the average of October 2018, 19 and 20. The way it works now is if I highlight October 2020 it only shows the averages for October 2020.
Is it possible to do what I'm trying to achieve and if so, how?
Thanks in advance.
Solved! Go to Solution.
Hi, @DaleWatkins
Based on your description, I created data to reproduce your scenario.The pbix file is attached in the end.
Table:
You may create a measure as below.
Avg =
var _year = SELECTEDVALUE('Table'[Date].[Year])
var _month = SELECTEDVALUE('Table'[Date].[MonthNo])
return
IF(
HASONEVALUE('Table'[Date].[Year])&&HASONEVALUE('Table'[Date].[Month]),
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
YEAR([Date])>=_year-2&&
YEAR([Date])<=_year&&
MONTH([Date])=_month
)
),
0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Something like this will work
Measure for same month last year = calculate([measure],sameperiodlastyear(calendar[date])
Measure for same month last to last year = calculate([Measure for same month last year],sameperiodlastyear(calendar[date])
Total = [measure]+[Measure for same month last year]+[Measure for same month last to last year]
Hope this helps.
Hi, @DaleWatkins
Based on your description, I created data to reproduce your scenario.The pbix file is attached in the end.
Table:
You may create a measure as below.
Avg =
var _year = SELECTEDVALUE('Table'[Date].[Year])
var _month = SELECTEDVALUE('Table'[Date].[MonthNo])
return
IF(
HASONEVALUE('Table'[Date].[Year])&&HASONEVALUE('Table'[Date].[Month]),
CALCULATE(
AVERAGE('Table'[Value]),
FILTER(
ALL('Table'),
YEAR([Date])>=_year-2&&
YEAR([Date])<=_year&&
MONTH([Date])=_month
)
),
0
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Essentially what I'm trying to achieve is for the gauge to show if the selected data is above the target data, which is an average of the selected data over the entirety of the db.
How I've got my gauge set up is:
Min is 60% average of 2018,19 and 20.
Max is 120% average of 2018, 19 and 20.
Target is 80% average of 2018, 19 and 20.
But this changes and gives me an avg of say one month if only October is selected.
What it should show is:
Red highlight = 237
Min = 126 not the 142 the 2nd pic shows.
Max = 252, not the 284 the 2nd pic shows.
Target = 167, not the 190 the 2nd pic shows.
Hopefully this clears up what I'm trying to achieve.
@DaleWatkins , not very clear. but you can show a value as target value and Min-max values.
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 |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |