Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Brave-month
Regular Visitor

Calculating difference within same measure and changing with slicer

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

a.PNG

 

 

 

This one when slicer is selected

 

1.PNG

 

 

 

Your help is much appreciated.

Thank you

2 ACCEPTED SOLUTIONS
v-eachen-msft
Community Support
Community Support

Hi @Brave-month ,

 

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

 

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

View solution in original post

RaulJimenez
Frequent Visitor

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

View solution in original post

4 REPLIES 4
RaulJimenez
Frequent Visitor

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

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

b.PNG

 

Thanks again for your help.

 

Regards

Manny

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
Community Support
Community Support

Hi @Brave-month ,

 

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

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.