How to show the latest value in card in powerbi

[Anonymous]

I have the following chart which shows sick leave per person for July and August and on top of that is a card that shows the average of all the months, so in this case is average of July and August which gives 1.55.

The table name is sick leave and my date column is called month/year and is in the format mmm-yy in the same table. 

Instead of the average, I want the card to show the latest value of the month, so in this case I want the card to show August value which is 0.79 and when September data is loaded, I want the card to show September data instead, unless someone clicks on a different month, then the card should update to show average of what they selected.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[danextian]

Hi @Anonymous,

The solution below assumes  that you dont have a separate dates table.

Create a calculated column of periods using DAX

PERIOD = 
FORMAT ( 'DataTable'[Date], "YYYYMM" )​Creat this measureCreate this measure

Create this measure below

Value of the latest month = 
VAR __maxperiod =
    CALCULATE ( MAX ( 'DataTable'[YYYYMM] ), ALL ( 'DataTable' ) )
RETURN
    CALCULATE (
        [Sum of Values],
        FILTER ( ALL ( 'DataTable' ), 'DataTable'[YYYYMM] = __maxperiod )
    )

sample result

[Anonymous]

The table name is sick leave and my date column is called month/year and is in the format mmm-yy in the same table. 

[amitchandak]

@Anonymous , You can try a measure like, Assume your table name is sales and net is the measure I used

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , Maxx(allselected(Sales), Sales[Date])),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

[Anonymous]

The table name is sick leave and my date column is called month/year and is in the format mmm-yy in the same table