Forum Discussion

Sultanista's avatar
Sultanista
Frequent Visitor
3 years ago
Solved

Calculate difference between current and previous row with a condition

I have a table and need to calculate a difference between 2 rows with a condition that it only applies to Agregation Type = All Branches - YTD and it is a diff between fiscal month and fiscal month-1...
DAX
Difference
powerbi
  • v-yinliw-msft's avatar
    v-yinliw-msft
    3 years ago

    Hi Sultanista ,

     

    You can try this method:

    New column:

     

    Diff =
VAR _curr =
    CALCULATE (
        SUM ( 'Table'[Applicants] ),
        'Table'[Agregation Type] = "All Branches - YTD"
    )
RETURN
    IF (
        [Fiscal Month] = 1
            || 'Table'[Agregation Type] <> "All Branches - YTD"
            || ISBLANK ( 'Table'[Applicants] ),
        BLANK (),
        _curr
            - CALCULATE (
                SUM ( 'Table'[Applicants] ),
                FILTER (
                    'Table',
                    [Agregation Type] = "All Branches - YTD"
                        && [Fiscal Year] = EARLIER ( 'Table'[Fiscal Year] )
                        && [Fiscal Month]
                            = EARLIER ( 'Table'[Fiscal Month] ) - 1
                )
            )
    )

     

    The result is:

     

     

    Hope this helps you. Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file whether it suits your requirement.

 

 

 

Diff CC =
VAR _previous =
    MAXX (
        FILTER (
            Data,
            Data[Fiscal Year] = EARLIER ( Data[Fiscal Year] )
                && Data[Fiscal Month]
                    = EARLIER ( Data[Fiscal Month] ) - 1
                && Data[Agregation Type] = "All Branches - YTD"
        ),
        Data[Applicants]
    )
RETURN
    IF (
        Data[Agregation Type] = "All Branches - YTD"
            && NOT ISBLANK ( Data[Applicants] ) && NOT ISBLANK ( _previous ),
        Data[Applicants] - _previous
    )