Calculating difference within same measure and changing with slicer

[Brave-month]

Hi Experts, This is my first post regarding Power bi and this is my first time creating dashboard in Power bi.

I have created a measure which is speed and I'm using slicer which is like production runs. what I want to do is create another measure showing difference between dates but also when I click on slicer it should pick last day for difference rather than calendar dates. I have reached to point where I have created measure showing the difference but it doesn't work when there I click on slicer. Please see below screenshots.

This one when no slicer is selected and shows the correct difference by creating below 2 measures

Measure = VAR vToday = MAX('Calendar'[Date])
VAR vYesterday = MAX('Calendar'[Date]) -1
RETURN CALCULATE ( [Measure Average Speed], 'Calendar'[Date] = vToday ) - CALCULATE ( [Measure Average Speed], 'Calendar'[Date] = vYesterday )

Measure 2 = IF([Measure]=[Measure Average Speed],0,[Measure])

This one when slicer is selected

Your help is much appreciated.

Thank you

[v-eachen-msft · Accepted solution]

Hi @Brave-month ,

You could add ALLSELECTED('Your slicer table'[Slicer column]) in your measure 2.

[RaulJimenez · Accepted solution]

The problem with your measure is that it has hard coded the date prior as the day -1 .  In the filtered table you don´t have concurrent days, so that creates a "prior day" that is not in there.

This article deals exactly with this issue, if it´s not clear let me know and I´ll help you.  (rjimenez@rringenieros.net)

Hope this helps!!

https://www.sqlbi.com/articles/optimizing-lastnonblank-and-lastnonblankvalue-calculations/?nu=92211

@lastnonblank

[Brave-month]

Hi Raul,

Thank you for your reply. It took me while to understand the whole coding (from your link) but based on that link I have created below 2 measures which works for me..

Measure PDSpeed = VAR vToday = MIN('Calendar'[Date])

VAR vYesterday = CALCULATETABLE(LASTNONBLANK('Calendar'[Date],[Measure Average Speed]),'Calendar'[Date] < vToday)

VAR vResult = CALCULATE([Measure Average Speed],vYesterday)

RETURN vResult

Measure DiffSpeed = IF (

HASONEVALUE ( 'Calendar'[Date] ),(IF([Measure Average Speed] = (IF (

NOT ISEMPTY ( 'Calendar' ),

VAR vToday = [Measure Average Speed]

VAR vYesterday = [Measure PDSpeed]

VAR Result = vToday - vYesterday

RETURN

Result

)),0,(IF (

NOT ISEMPTY ( 'Calendar' ),

VAR vToday = [Measure Average Speed]

VAR vYesterday = [Measure PDSpeed]

VAR Result = vToday - vYesterday

RETURN

Result

)))),

SUMX ( VALUES ( 'Calendar'[Date] ),(IF([Measure Average Speed] = (IF (

NOT ISEMPTY ( 'Calendar' ),

VAR vToday = [Measure Average Speed]

VAR vYesterday = [Measure PDSpeed]

VAR Result = vToday - vYesterday

RETURN

Result

)),0,(IF (

NOT ISEMPTY ( 'Calendar' ),

VAR vToday = [Measure Average Speed]

VAR vYesterday = [Measure PDSpeed]

VAR Result = vToday - vYesterday

RETURN

Result

)))))

) 

Thanks again for your help.

Regards

Manny

[RaulJimenez]

My pleasure!!  I´m sorry it took me so long to reply, I hadn´t been back here in a while.  Im glad that helped!!

It also took me a while to wrap my head around the LASTNONBLANK logic. I have found it very powerful and usefull since!!

Regards,

Raul

[v-eachen-msft · Accepted solution]

Hi @Brave-month ,

You could add ALLSELECTED('Your slicer table'[Slicer column]) in your measure 2.