A card for a measure

[Anonymous]

I have the following Table:

   

So at any given month year the actual HC is given. And i calculate the avg (say for 2/28/2019 the Avg rolling 2 months is [Actual value on 1/31/2019 + Actual value on 2/28/2019 ] / 2). 

I already have a logic in placxe to calculate this average rolling 2 months in power BI as follows:

However, on my front end I only want to display a card that shows me the avg HC value as on 3/31/2019. And the reason I want to do this is, I want to show 2 cards - one for 3/31/2019 and one as on 12/31/2018 and compare the % difference of the 2.

- I can show the avg HC on the card by choosing visual filters, but that does not help me calculate the % difference.

Any help on this?

[v-frfei-msft · Accepted solution]

Hi @Anonymous ,

One sample for your reference. Please check the following steps as below.

1. Create two calculated tables as below. Please notice here we should not create relationship between tables.

datefiler1 = VALUES(Table1[MonthYear])

datefiler2 = VALUES(Table1[MonthYear])

2. Create a measures to get the excepted result.

Measure 1 = 
var sele1 = SELECTEDVALUE(datefiler1[MonthYear])
var mindate = MINX(ALL('Table1'),Table1[MonthYear])
var rolling = CALCULATE(SUM(Table1[HC]),DATESINPERIOD('Table'[Date],sele1,-2,MONTH))
return
IF(sele1=mindate,rolling,rolling/2)

Measure 2 = 
var sele1 = SELECTEDVALUE(datefilter2[MonthYear])
var mindate = MINX(ALL('Table1'),Table1[MonthYear])
var rolling = CALCULATE(SUM(Table1[HC]),DATESINPERIOD('Table'[Date],sele1,-2,MONTH))
return
IF(sele1=mindate,rolling,rolling/2)

difference = DIVIDE([Measure 1]-[Measure 2],[Measure 2])

[Anonymous]

@v-frfei-msft  Hey does this work only if we choose a slicer? 

Also could you tell me the role of MINX? Because i even have dates in 2017 and every month the Month year will be expanfing in 2019 as the months go by

[v-frfei-msft]

Hi @Anonymous ,

We cannot make it work by one slicer here. 

And the minx was used to get the min date in the fact table. So for the min date, the Sum of HC of previous month is blank. It worked as your varlm in your measure.

[v-frfei-msft]

Hi @Anonymous ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.