Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

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

9 Replies

  • lukiz84's avatar
    lukiz84
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi,

     

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


RETURN CALCULATE (
    AVERAGE ( 'Table'[Sick leave] ),
    FILTER ( 'Table', MONTH ( 'Table'[Month/Year] ) = maxMonthTable )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        lukiz84
        Icon for Memorable Member rankMemorable Member
        3 years ago

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