Calculatings Days overdue from today

Hi Everyone

I’m going around in circles in which i am sure is a very simple calculation but i cannot find the right formula to achieve the number of days past due based on today’s date

My data is simple

In my fact table i have a list of people who have training records and i have a completed date and a re certification date. i have a date table with a active relationship to the Completed Date in the fact table.

What i want to show is the number of days past due from the re-certification date based on today’s date for the training for any given person

So for example today is 24/06/2020 and a re-certification date is 28/03/2020 how many days have past. (with the number of days past due being dynamic based on the todays date)

image

I have checked out the time intelligence resource and cohort analysis course but have yet to find the information that gives me the solution im after

Help please

@AoakeP,

Try this:

Days Overdue = 
IF(
    SELECTEDVALUE('Table'[Recertification]) >= today(),
    0,
    DATEDIFF(
        SELECTEDVALUE( 'Table'[Recertification] ),
        TODAY(),
        DAY
    )
)

image

  • Brian
2 Likes

Hello @AoakeP,

Thank you for posting your query onto the Forum.

Since solution has already been provided by @BrianJ.

You can also check out this formula, if it works for you -

Number of Days Past Due = 
VAR LastDate = TODAY()

RETURN
VALUE( Recertify Date - LastDate )

Thanks & Warm Regards,
Harsh

1 Like

Thanks @Brian this worked perfectly

Thanks to you also @Harsh for replying and giving me another calculation to try :slight_smile:

Hello @AoakeP,

You’re welcome. :slightly_smiling_face:

Glad we’re able to help you out.

Thanks & Warm Regards,
Harsh