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.

 In the table I added a column Diff that should show those results. # of rows will grow and is added on a weekly basis.

 

 

Link_keyFiscal YearFiscal MonthFiscal WeekAgregation TypeApplicantsDiff
2021-1-All Branches - YTD202114All Branches - YTD25 840 
2021-2-All Branches - YTD202128All Branches - YTD50 13324 293
2021-3-All Branches - YTD2021313All Branches - YTD75 08924 956
2021-4-All Branches - YTD2021417All Branches - YTD94 88819 799
2021-5-All Branches - YTD2021521All Branches - YTD117 86622 978
2021-6-All Branches - YTD2021626All Branches - YTD151 61233 746
2021-7-All Branches - YTD2021730All Branches - YTD183 44731 835
2021-8-All Branches - YTD2021834All Branches - YTD217 27533 828
2021-9-All Branches - YTD2021939All Branches - YTD262 46945 194
2021-10-All Branches - YTD20211043All Branches - YTD303 90041 431
2021-11-All Branches - YTD20211147All Branches - YTD343 32439 424
2021-12-All Branches - YTD20211252All Branches - YTD386 62143 297
2022-1-All Branches - YTD202214All Branches - YTD37 203 
2022-2-All Branches - YTD202228All Branches - YTD69 59432 391
2022-3-All Branches - YTD2022313All Branches - YTD106 12936 535
2022-4-All Branches - YTD2022417All Branches - YTD139 62933 500
2022-5-All Branches - YTD2022521All Branches - YTD175 91736 288
2022-6-All Branches - YTD2022626All Branches - YTD227 16051 243
2022-7-All Branches - YTD2022730All Branches - YTD269 02041 860
2022-8-All Branches - YTD2022834All Branches - YTD306 48737 467
2022-9-All Branches - YTD2022939All Branches - YTD354 04147 554
2021-1-All Branches - MTD202114All Branches - MTD  
2021-2-All Branches - MTD202128All Branches - MTD  
2021-3-All Branches - MTD2021313All Branches - MTD  
2021-4-All Branches - MTD2021417All Branches - MTD  
2021-5-All Branches - MTD2021521All Branches - MTD  
2021-6-All Branches - MTD2021626All Branches - MTD  
2021-7-All Branches - MTD2021730All Branches - MTD  
2021-8-All Branches - MTD2021834All Branches - MTD  
2021-9-All Branches - MTD2021939All Branches - MTD  
2021-10-All Branches - MTD20211043All Branches - MTD  
2021-11-All Branches - MTD20211147All Branches - MTD  
2021-12-All Branches - MTD20211252All Branches - MTD  
2022-1-All Branches - MTD202214All Branches - MTD  
2022-2-All Branches - MTD202228All Branches - MTD  
2022-3-All Branches - MTD2022313All Branches - MTD  
2022-4-All Branches - MTD2022417All Branches - MTD  
2022-5-All Branches - MTD2022521All Branches - MTD  
2022-6-All Branches - MTD2022626All Branches - MTD  
2022-7-All Branches - MTD2022730All Branches - MTD  
2022-8-All Branches - MTD2022834All Branches - MTD  
2022-9-All Branches - MTD2022939All Branches - MTD  
2022-1-SC Branches - YTD202214SC Branches - YTD  
2022-2-SC Branches - YTD202228SC Branches - YTD  
2022-3-SC Branches - YTD2022313SC Branches - YTD  
2022-4-SC Branches - YTD2022417SC Branches - YTD  
2022-5-SC Branches - YTD2022521SC Branches - YTD  
2022-6-SC Branches - YTD2022626SC Branches - YTD  
2022-7-SC Branches - YTD2022730SC Branches - YTD  
2022-8-SC Branches - YTD2022834SC Branches - YTD  
2022-9-SC Branches - YTD2022939SC Branches - YTD  
2022-1-SC Branches - MTD202214SC Branches - MTD3 358 
2022-2-SC Branches - MTD202228SC Branches - MTD3 049 
2022-3-SC Branches - MTD2022313SC Branches - MTD3 308 
2022-4-SC Branches - MTD2022417SC Branches - MTD3 018 
2022-5-SC Branches - MTD2022521SC Branches - MTD3 516 
2022-6-SC Branches - MTD2022626SC Branches - MTD5 445 
2022-7-SC Branches - MTD2022730SC Branches - MTD4 053 
2022-8-SC Branches - MTD2022834SC Branches - MTD4 077 
2022-9-SC Branches - MTD2022939SC Branches - MTD4 283 
2022-10-All Branches - YTD20221042All Branches - YTD382 55928518
2022-10-All Branches - MTD20221042All Branches - MTD  
2022-10-SC Branches - YTD20221042SC Branches - YTD  
2022-10-SC Branches - MTD20221042SC Branches - MTD2 548 
2022-10-All Branches - YTD20221043All Branches - YTD391 34937308
2022-10-All Branches - MTD20221043All Branches - MTD  
2022-10-SC Branches - YTD20221043SC Branches - YTD  
2022-10-SC Branches - MTD20221043SC Branches - MTD3 344 
2022-11-All Branches - YTD20221144All Branches - YTD  
2022-11-All Branches - MTD20221144All Branches - MTD  
2022-11-SC Branches - YTD20221144SC Branches - YTD  
2022-11-SC Branches - MTD20221144SC Branches - MTD  
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.

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
    )
  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    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.

    • Sultanista's avatar
      Sultanista
      Frequent Visitor
      3 years ago

      It works great! Is it possible to move those results from current place to the corresponding fiscal year and month but aggregation type = All Branches - MTD ? 

      And having the 1st value of the year (month =1) same as in YTD?