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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
cetobs
Frequent Visitor

Calculated DateDiff column for ranked rows

Dear PowerBI Gurus,

 

I need your help in calculating the datediff as a calculated column (in colum I:- APRD DateDiff). At the moment, I can't seem to figure out the logic to make this work.

To get started, I reckon I needed to rank each address (column C) by their APRDates (column D), which I managed to do (in colum H) as shown in the attached sample data. The end goal, however, is to find the datediff between RankN and RankN-1 APRDates for each distinct address. i.e for 77D Lucas Road, the first DateDiff is 20days (24//11/2015 - 04/11/2015), etc.


If you need any further clarification, please let me know. I look forward to speedy assistance.

 

Many thanks.

 

https://www.dropbox.com/s/bnm2s20neosvep1/DateDiff%20Sample%20Data.xlsx?dl=0

 

DateDiff Sample Date.png

1 ACCEPTED SOLUTION

Hi @Zubair_Muhammad,

 

Thanks for giving me the headstart.

Although, I had to tweak your formular ever so slightly to make it work. So, basically declaring Vars for:

-Min (previous_date),

-Max (next_date) dates,

-Each address line (X1)

and then returning DateDiff between Min and Max got the job done!

 

Here's what the final solution looked like.

 

DaysDifference =

Var X1 = Table1[Address]
VAR previous_date =
CALCULATE (
MIN ( Table1[APRDate] ),FILTER(Table1,Table1[Address] =X1)
FILTER (
ALLEXCEPT ( Table1, Table1[Address] ),
Table1[APRDate Rank]
= EARLIER ( Table1[APRDate Rank] ) - 1
)
)
VAR next_date =
CALCULATE (
MAX ( Table1[APRDate] ),FILTER(Table1,Table1[Address] =X1),
FILTER (
ALLEXCEPT ( Table1, Table1[Address] ),
Table1[APRDate Rank]
= EARLIER ( Table1[APRDate Rank] )
)
)
RETURN
IF (
NOT ( ISBLANK ( previous_date ) ),
DATEDIFF ( previous_date, next_date, DAY )
)

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

HI @cetobs

 

Try this column

 

DaysDifference =
VAR previous_date =
    CALCULATE (
        MAX ( Table1[APRDate] ),
        FILTER (
            ALLEXCEPT ( Table1, Table1[Address] ),
            Table1[APRDate Rank]
                = EARLIER ( Table1[APRDate Rank] ) - 1
        )
    )
RETURN
    IF (
        NOT ( ISBLANK ( previous_date ) ),
        DATEDIFF ( previous_date, Table1[APRDate], DAY )
    )

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

 

Thanks for giving me the headstart.

Although, I had to tweak your formular ever so slightly to make it work. So, basically declaring Vars for:

-Min (previous_date),

-Max (next_date) dates,

-Each address line (X1)

and then returning DateDiff between Min and Max got the job done!

 

Here's what the final solution looked like.

 

DaysDifference =

Var X1 = Table1[Address]
VAR previous_date =
CALCULATE (
MIN ( Table1[APRDate] ),FILTER(Table1,Table1[Address] =X1)
FILTER (
ALLEXCEPT ( Table1, Table1[Address] ),
Table1[APRDate Rank]
= EARLIER ( Table1[APRDate Rank] ) - 1
)
)
VAR next_date =
CALCULATE (
MAX ( Table1[APRDate] ),FILTER(Table1,Table1[Address] =X1),
FILTER (
ALLEXCEPT ( Table1, Table1[Address] ),
Table1[APRDate Rank]
= EARLIER ( Table1[APRDate Rank] )
)
)
RETURN
IF (
NOT ( ISBLANK ( previous_date ) ),
DATEDIFF ( previous_date, next_date, DAY )
)

cetobs
Frequent Visitor

Dear PowerBI Gurus,

 

I need your help in calculating the datediff as a calculated column (in colum I:- APRD DateDiff). At the moment, I can't seem to figure out the logic to make this work. 

 

To get started, I reckon I needed to rank each address (column C) by their APRDates (column D), which I managed to do (in colum H) as shown in the attached sample data. The end goal, however, is to calculate the datediff between RankN and RankN-1 APRDates for each distinct address. i.e for 77D Lucas Road, the first DateDiff is 20days  (24//11/2015 - 04/11/2015), etc.

 

If you need any further clarification, please let me know. I look forward to speedy assistance.

 

Many thanks.

 

 https://www.dropbox.com/s/bnm2s20neosvep1/DateDiff%20Sample%20Data.xlsx?dl=0

 

DateDiff Sample Date.png

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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