Forum Discussion

TobiasWiegand's avatar
TobiasWiegand
Regular Visitor
2 years ago
Solved

Compare different statuses measure

Hello, 
I have a table which represents different values for different time frames on different status days (current status). E.g. value from status 23.05. for YYYYMM 202402 is 236520 and value from status 23.06. for YYYYMM 202402 is 149040.

 

I want to create a measure that shows the difference from current status 23.06 to the previous status (23.05.) - here 236520-149040= 87480 like I can do in a pivot in xls as "show difference from previous current status". To choose which current statuses to compare from, I would set two filters which 1. base filter 2. compare filter two select the two statuses for comparison. How can I do this?

 

Thank you!

 

 

 

  • FreemanZ's avatar
    FreemanZ
    2 years ago

    hi TobiasWiegand ,

     

    not sure if i fully get you, supposing you have a data table like:

     

    try to

    1) plot two slicers with two calculated tables like:

    Slicer1 = ALL(data[Status])
    Slicer2 = ALL(data[Status])

     

    2) plot a card visual with a measure like:

    Diff = 
MAXX(
    FILTER(data,data[Status]=SELECTEDVALUE(Slicer2[Status])),
    data[Qty]
) -
MAXX(
    FILTER(data,data[Status]=SELECTEDVALUE(Slicer1[Status])),
    data[Qty]
)

     

    it worked like:

     

     

3 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, TobiasWiegand 

     

    try below code 

    Measure 2 = 
var a = MAXX(
           FILTER(
              ALL('Table (2)'),
              'Table (2)'[current status]<MAX('Table (2)'[current status])
           ),
           'Table (2)'[qty]
        )
return
SUM('Table (2)'[qty])-a
    • TobiasWiegand's avatar
      TobiasWiegand
      Regular Visitor
      2 years ago

      Hi Dangar, 

       

      thanks for your reply. The above solution doesn't work because the field "current status" is a text field and needs to stay one. Therefore PBI cannot compare which one is smaller. 

       

      I would simply Like to Have 2 filters:

       

      Filter 1 (Base Status): 23.05.
      Filter 2 (Compare Compare): 23.06.

      leading to a table showing

      23.05.         23.06.
      236520      -87480

      • FreemanZ's avatar
        FreemanZ
        Super User
        2 years ago

        hi TobiasWiegand ,

         

        not sure if i fully get you, supposing you have a data table like:

         

        try to

        1) plot two slicers with two calculated tables like:

        Slicer1 = ALL(data[Status])
        Slicer2 = ALL(data[Status])

         

        2) plot a card visual with a measure like:

        Diff = 
MAXX(
    FILTER(data,data[Status]=SELECTEDVALUE(Slicer2[Status])),
    data[Qty]
) -
MAXX(
    FILTER(data,data[Status]=SELECTEDVALUE(Slicer1[Status])),
    data[Qty]
)

         

        it worked like: