Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sultanista
Frequent Visitor

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  
1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

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:

vyinliwmsft_0-1668497607255.png

 

 

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.

View solution in original post

3 REPLIES 3
v-yinliw-msft
Community Support
Community Support

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:

vyinliwmsft_0-1668497607255.png

 

 

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.

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?

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1668486168751.png

 

 

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
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors