Forum Discussion

corange's avatar
corange
Post Patron
6 years ago

Calculate difference from value in same column and based on data from another

Hi POWER BI Team, 

 

I need your help and I will try my best to be clear. 

 

My dataset has three main column CLIENT, DATE (Text) and ORDER(Numeric). When I drop the column into a MATRIX, this is what I obtain: 

 

 

Using DAY as a filter, I will be comparing a DAY at a time to TODAY. I would like to be able to calculate the difference and the difference % in ORDER between Today Value and Monday Value and for the measure to update when I select any other day of the week. I will always be comparing any DAY with TODAY. 

 

Here the desired outcome: 

 

Is there any way I can achieve this using DAX maybe? If yes, can someone help me reach the desired outcome? 

 

Thank you in advance. 

Need Help

3 Replies

    • corange's avatar
      corange
      Post Patron
      6 years ago

      Hi  ,

       

      Here my sample: https://1drv.ms/u/s!AiS7XVNuQsBCbslVeWs3M66_7po?e=C3CSAB 

       

      Here a table of what i would like to achieve: 

       

      Row LabelsMondayTodayDifference% Row LabelsTuesdayTodayDifference%
      Client 11 -1-100% Client 11 -1-100%
      Client 131100% Client 114 -4-100%
      Client 151 -1-100% Client 131100%
      Client 31 -1-100% Client 141 -1-100%
      Client 44 -4-100% Client 21 -1-100%
      Client 51.2520.7560% Client 42.25 -2.25-100%
      Client 72.675.002.3387% Client 52200%
      Client 81 -1-100% Client 61 -1-100%
      Client 91 -1-100% Client 7154400%
            Client 82 -2-100%
      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        6 years ago

        hi  corange 

        Try this way as below:

        Step1:

        Add a BookingDayNo column in the table.

        Step2:

        Create two measure as below:

        Difference = 
var _firstday=CALCULATE(MIN('Table'[BookingDayNo]),ALLSELECTED('Table'))
var _lastday=CALCULATE(MAX('Table'[BookingDayNo]),ALLSELECTED('Table'))
return
CALCULATE(SUM('Table'[OrderNumber]),FILTER(ALL('Table'[BookingDayName],'Table'[BookingDayNo]),'Table'[BookingDayNo]=_lastday))-CALCULATE(SUM('Table'[OrderNumber]),FILTER(ALL('Table'[BookingDayName],'Table'[BookingDayNo]),'Table'[BookingDayNo]=_firstday))


% = 
var _firstday=CALCULATE(MIN('Table'[BookingDayNo]),ALLSELECTED('Table'))
var _lastday=CALCULATE(MAX('Table'[BookingDayNo]),ALLSELECTED('Table'))
return
DIVIDE(CALCULATE(SUM('Table'[OrderNumber]),FILTER(ALL('Table'[BookingDayName],'Table'[BookingDayNo]),'Table'[BookingDayNo]=_lastday))-CALCULATE(SUM('Table'[OrderNumber]),FILTER(ALL('Table'[BookingDayName],'Table'[BookingDayNo]),'Table'[BookingDayNo]=_firstday)),CALCULATE(SUM('Table'[OrderNumber]),FILTER(ALL('Table'[BookingDayName],'Table'[BookingDayNo]),'Table'[BookingDayNo]=_firstday)))

         

        Result:

         

        By the way, it could not achieve that add custom column in the end of matrix visual, so you'd better put them in two visuals

        Regards,
        Lin