Show latest value on card

[Anonymous]

 

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?

[lukiz84]

Hi,

Latest value =
VAR maxMonthTable =
   MONTH(MAX('Table'[Month/Year]))


RETURN CALCULATE (
    AVERAGE ( 'Table'[Sick leave] ),
    FILTER ( 'Table', MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)



               

[Anonymous]

@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]

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 )
)

[Anonymous]

@lukiz84 

Sorry does not work, shows up as blank now 

[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 )
)

[Anonymous]

@lukiz84 

Do not have date table in place 

[lukiz84]

ok did you try my latest measure?

[Anonymous · Accepted solution]

@lukiz84 

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]

fine, please mark my answer as solution 😉