Forum Discussion
Show latest value on card
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 colum...
Thank you, yep it worked, solution below
Latest value =VAR maxMonthTable =CALCULATE(MONTH(MAX('Table'[MONTH/YEAR])),ALL('Table'))RETURN CALCULATE (AVERAGE ( 'Table'[SICK LEAVE TOTAL DAYS]),FILTER ( ALL('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.
lukiz84
Memorable Member
Memorable MemberHi,
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 )
)- lukiz84
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 ) )