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.
I'm a newcomer to PowerBI and DAX, and I'm in need of help.
Given a dataset
Sales = (Product, Units Sold, Date)
I'd like to display a gauge where:
The "Value" should be the sum of all Units Sold yesterday
The "Target Value" should be the average sum of all Units Sold each Date in the time period defined by the visual level filter
The "Maximum Value" should the the maximum sum of all Units Sold each Date in the time period defined by the visual level filter.
I think I have 2 of 3 solved.
_first_date_of_filter = MIN('Sales'[Date])
_last_date_of_filter = MAX('Sales'[Date])
_value = SUM('Sales'[Units Sold]) / DATEDIFF([_first_date_of_filter],[_last_date_of_filter],DAY)
_target_value = CALCULATE(SUM('Sales'[Units Sold]),PREVIOUSDAY('Sales'[Date]))
But I can't work out how to calculate the total for each day, then to grab the maximum of those totals.
Any ideas?
Solved! Go to Solution.
Hi,
When you are doing Time-Intelligence calculations, it is much better to have a proper (and separate) Calendar Table.
See here: https://exceleratorbi.com.au/power-pivot-calendar-tables/
Once you have this calendar table, link it to your Sales Table with the Dates column (1 to Many relationship) and use this measure:
MaxValue = MAXX( Values(Calendar[Date] ) , Calculate(Sum(Sales[Units Sold] ) ) )
or, even better, store first this measure:
TotalUnits = Sum(Sales[Units Sold] )
and then use this one :
MaxValue = MAXX( Values(Calendar[Date] ) , [TotalUnits] )
Hi,
When you are doing Time-Intelligence calculations, it is much better to have a proper (and separate) Calendar Table.
See here: https://exceleratorbi.com.au/power-pivot-calendar-tables/
Once you have this calendar table, link it to your Sales Table with the Dates column (1 to Many relationship) and use this measure:
MaxValue = MAXX( Values(Calendar[Date] ) , Calculate(Sum(Sales[Units Sold] ) ) )
or, even better, store first this measure:
TotalUnits = Sum(Sales[Units Sold] )
and then use this one :
MaxValue = MAXX( Values(Calendar[Date] ) , [TotalUnits] )
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |