Calculate difference between 2 cycles for each status

[Milan14]

Hello everyone,

I would like to calculate the difference between 2 cycles for each status.

For example when I applied filter Cycle = Sept, 

For Status = AAA

i. YearMonth = Sept-22, the output value will be 

[Cycle = Sept, YearMonth = Sep-22] - [[Cycle = Aug, YearMonth = Aug-22] + [Cycle = Aug, YearMonth = Sep-22]]

Eg: Output value = [40] - [(16) + (43)] = -19

ii. YearMonth = Oct-22, the output value will be

[Cycle = Sept, YearMonth = Oct-22] - [Cycle = Aug, YearMonth = Oct-22]

Eg: Output value = [-30] - [-26] = -4

For Status = BBB

i. YearMonth = Sep-22, the output value will be

[Cycle = Sept, YearMonth = Sep-22] - [Cycle = Aug, YearMonth = Sep-22]

*Note: This logic will be applied to the consecutive YearMonth

Eg: Output value = [26] - [11] = 15

Sample Data:

Expected Outcome in table:

Much appreciate your help.

Thanks.

[Anonymous · Accepted solution]

Hi @Milan14 ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can follow the steps below to get it:

1. Create a year month dimension table

YearMonth = VALUES('Table'[YearMonth])

2. Create two measures as below to get the values

Measure = 
VAR _selstatus =
    SELECTEDVALUE ( 'Table'[Status] )
VAR _selym =
    SELECTEDVALUE ( 'YearMonth'[YearMonth] )
VAR _sptvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            'Table',
            'Table'[Cycle] = "Sept"
                && 'Table'[YearMonth] = _selym
                && 'Table'[Status] = _selstatus
        )
    )
VAR _augvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = _selstatus
                && 'Table'[YearMonth] = _selym
                && 'Table'[Cycle] = "Aug"
        )
    )
VAR _preomonth =
    DATE ( YEAR ( _selym ), MONTH ( _selym ), 1 ) - 1
VAR _pmvalue =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Status] = _selstatus
                && FORMAT ( 'Table'[YearMonth], "mmm-yy" ) = FORMAT ( _preomonth, "mmm-yy" )
                && 'Table'[Cycle] = "Aug"
        )
    )
RETURN
    IF (
        _selstatus = "AAA"
            && FORMAT ( _selym, "mmm-yy" ) = "Sep-22",
        _sptvalue - ( _augvalue + _pmvalue ),
        _sptvalue - _augvalue
    )

Nvalue = 
SUMX (
    VALUES ( 'Table'[Status] ),
    SUMX ( VALUES ( 'YearMonth'[YearMonth] ), [Measure] )
)

Best Regards