Forum Discussion

Brave-month's avatar
Brave-month
Regular Visitor
6 years ago
Solved

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

 

 

 

This one when slicer is selected

 

 

 

 

Your help is much appreciated.

Thank you

Need Help

4 Replies

  • RaulJimenez's avatar
    RaulJimenez
    Frequent Visitor
    6 years ago

    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.  ([email protected])

    Hope this helps!!


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

    @lastnonblank

    • Brave-month's avatar
      Brave-month
      Regular Visitor
      6 years ago

      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's avatar
        RaulJimenez
        Frequent Visitor
        6 years ago

        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