Compare different statuses measure

[TobiasWiegand]

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!

[Dangar332]

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]

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 · Accepted solution]

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: