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 have the following chart, with a card above the chart that shows average of total sick days and latest value.
The way my data model is that I have a table called Sales with columns like Month, Month/Year in format mmmm-yyyy, and Sick Leave.
Latest value = CALCULATE(AVERAGE('Table'[Sick leave]),FILTER('Table',MONTH('Table'[Month/Year]) = MONTH(TODAY()))) Average of sick leave = AVERAGE('Table'[Sick leave])
However what this does is that if the current month is October, the latest value will show as blank if the October data is not available. Sometimes the data is not received until 2nd week of month and I don't want the card showing blank, for example if october data is not ready, rather than showing blank I want it showing september value until october value has been added. Is it possible?
Also when selecting other columns, is it possible to keep the latest value card as not blank?
Solved! Go to Solution.
Thank you, yep it worked, solution below
Hi,
Latest value =
VAR maxMonthTable =
MONTH(MAX('Table'[Month/Year]))
RETURN CALCULATE (
AVERAGE ( 'Table'[Sick leave] ),
FILTER ( 'Table', MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)
@lukiz84 Thank you! Is it possible for when selecting different months, the latest value remains the same as it is. For example when picking July and August, I want the latest available data (which in this case is September) to show up instead of August value.
Hi,
sure, sorry:
Latest value =
VAR maxMonthTable =
CALCULATE(
MONTH(MAX('Table'[Month/Year])),
ALL('Table')
)
RETURN CALCULATE (
AVERAGE ( 'Table'[Sick leave] ),
FILTER ( 'Table', MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)
or if you have a date table in place (i'm not sure if you do):
Latest value =
VAR maxMonthTable =
CALCULATE(
MONTH(MAX('Table'[Month/Year])),
ALL('Dates')
)
RETURN CALCULATE (
AVERAGE ( 'Table'[Sick leave] ),
FILTER ( 'Table', MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)
Do you have a date table in place?
but try
Latest value =
VAR maxMonthTable =
CALCULATE(
MONTH(MAX('Table'[Month/Year])),
ALL('Table[Month/Year]')
)
RETURN CALCULATE (
AVERAGE ( 'Table'[Sick leave] ),
FILTER ( ALL('Table'), MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)
ok did you try my latest measure?
Thank you, yep it worked, solution below
fine, please mark my answer as solution 😉
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 |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |