cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Akshaya Member
Member

A card for a measure

I have the following Table:

 

Step 1.PNG   

 

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:

 

Step 2.PNG

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: A card for a measure

Hi @Akshaya ,

 

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

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Support Team
Community Support Team

Re: A card for a measure

Hi @Akshaya ,

 

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

Capture.PNG

 

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Akshaya Member
Member

Re: A card for a measure

@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

Community Support Team
Community Support Team

Re: A card for a measure

Hi @Akshaya ,

 

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.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team
Community Support Team

Re: A card for a measure

Hi @Akshaya ,

 

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

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.