Forum Discussion

Milan14's avatar
Milan14
Frequent Visitor
3 years ago
Solved

Calculate difference between 2 cycles for each status

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's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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