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
GuillemXII
Helper I
Helper I

Reference Previous Row for Calculated Column

Hi everyone, 

 

I have the following thable where I have 3 columns:

 

Data.jpg

 

What I need to doo, is to add a new column where it indicates in a binary code ( 0 , 1) wether the new register is in fact a new absence (1) or just a continuity of a previous one (0). 

 

To do that, I need to make sure that both registers belong to the same worker, and then analyse if were sequential in time (at least 4 days between them) and corresponded to the same type of absence. 

In this regard, form the sample, from "02/01/2019" to "14/01/2019" it would represent the same absence while the register starting in "30/01/2019" would be a new one. 

 

In excel language, in order to get that new column would be as simple as: IF(AND(A1=A2;C1=C2;B2-B1+1<5);0;1). 

 

However, this is an unpivoted table from Excel and I've no idea how to reference in DAX language that "previous row" in order to compare it with the "current one" in an "if" function for the new calculated column. 

Any tips and examples you can bring will be much appriciated!

Thank you all!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

Hello @GuillemXII 

Give this a try.  It filters the table based on the info from the current row then counts the number of rows returned.  If the count is >= 1 it is a continuing absence.

Check = 
VAR CurrWorker = 'Table'[Worker Code]
VAR CurrDate = 'Table'[Date]
VAR AbsType = 'Table'[Type Absence]
RETURN
IF ( 
    ISBLANK (
        COUNTROWS(
            FILTER('Table',
            'Table'[Worker Code] = CurrWorker &&
            'Table'[Type Absence] = AbsType &&
            'Table'[Date] < CurrDate &&
            'Table'[Date] >= CurrDate -5
            )
        )
    ),1,0)

absencecount.jpg

View solution in original post

2 REPLIES 2
jdbuchanan71
Super User
Super User

Hello @GuillemXII 

Give this a try.  It filters the table based on the info from the current row then counts the number of rows returned.  If the count is >= 1 it is a continuing absence.

Check = 
VAR CurrWorker = 'Table'[Worker Code]
VAR CurrDate = 'Table'[Date]
VAR AbsType = 'Table'[Type Absence]
RETURN
IF ( 
    ISBLANK (
        COUNTROWS(
            FILTER('Table',
            'Table'[Worker Code] = CurrWorker &&
            'Table'[Type Absence] = AbsType &&
            'Table'[Date] < CurrDate &&
            'Table'[Date] >= CurrDate -5
            )
        )
    ),1,0)

absencecount.jpg

Thanks @jdbuchanan71 !

 

That worked perfectly!

 

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.