How to get Duration from Start and End date and if End date is Blank then use last refreshed date

[OPS-MLTSD]

Hello,

I have a couple of columns in my PBI report, two of those columns in my table are "Service Start Date" and "Service End Date". I know how to get the duration in months from the start and end date using the DATEDIFF function. However, there are a lot of NULLS in the "Service End Date" column, in those instances, I would use the Last Refresh Date as my End Date. 

I have two tables in my PBI report: one is called Service and the other table is called Last Refresh Date. The report is not reshreshed daily but is refreshed when data is required. I was wondering if someone could please let me know how I can get the service duration 

So Far, I have tried this but it did nt work:

Duration = IF(ISBLANK('Service'[Service End Date]),DATEDIFF('Service'[Service Start Date],'Last Refresh Date'[DATE],MONTH),('Service'[Service Start Date], 'Service'[Service End Date], MONTHS))

Thank you!

[jdbuchanan71]

If you want the LastRefresh on a Friday to be that days date, meaning it does not roll back to the previous Friday, it would be like this.

Duration =
    VAR _ServStart = Service[Service Start Date]
    VAR _ServEnd = Service[Service End Date]
    VAR _WeekDay = WEEKDAY ( TODAY () )
    VAR _LastRefresh = TODAY () - IF ( _WeekDay > 5, _WeekDay - 6, _WeekDay + 1 )
    VAR _EndDate = IF ( ISBLANK ( _ServEnd ), _LastRefresh, _ServEnd )
RETURN
    DATEDIFF ( _ServStart, _EndDate, MONTH )

[jdbuchanan71]

That would be like this.

Duration = 
    VAR _ServStart = Service[Service Start Date]
    VAR _ServEnd = Service[Service End Date]
    VAR _LastRefresh = TODAY() - WEEKDAY( TODAY() ) - 1
    VAR _EndDate = IF ( ISBLANK ( _ServEnd ), _LastRefresh, _ServEnd )
RETURN 
    DATEDIFF ( _ServStart, _EndDate, MONTH )

TODAY() - WEEKDAY( TODAY() ) = previous Sat.  Then we -1 to go back to Fri.

[OPS-MLTSD]

thank you so much, I really appreciate it, so this formular will alway grab the last Friday?

[jdbuchanan71]

Opps, my formula was not handling Sat properly and was going back to the Fri before last Fri.  This will roll Sat back to the previous day (Fri) and on a Friday it will be the previous Friday.

Duration = 
    VAR _ServStart = Service[Service Start Date]
    VAR _ServEnd = Service[Service End Date]
    VAR _LastRefresh = TODAY() - IF ( WEEKDAY ( TODAY() ) = 7, 0, WEEKDAY ( TODAY() ) ) - 1
    VAR _EndDate = IF ( ISBLANK ( _ServEnd ), _LastRefresh, _ServEnd )
RETURN 
    DATEDIFF ( _ServStart, _EndDate, MONTH )

Here is what the VAR _LastRefresh would calc on each date in August:

[OPS-MLTSD]

Thank you so much! this is super useful info to know 🙂

[jdbuchanan71]

If you want it as a measure something like this would work.

Duration Measure = 
VAR _LastRefresh =
    MAX ( 'Last Refresh Date'[Date] )
RETURN
    MAXX (
        Service,
        DATEDIFF (
            Service[Service Start Date],
            IF ( ISBLANK ( Service[Service End Date] ), _LastRefresh, Service[Service End Date] ),
            MONTH
        )
    )

[jdbuchanan71 · Accepted solution]

@OPS-MLTSD 

It sounds like you want this as a calculated column in your Service table yes?  Something like this should work.

Duration = 
    VAR _ServStart = Service[Service Start Date]
    VAR _ServEnd = Service[Service End Date]
    VAR _LastRefresh = MAX('Last Refresh Date'[Date])
    VAR _EndDate = IF ( ISBLANK ( _ServEnd ), _LastRefresh, _ServEnd )
RETURN 
    DATEDIFF ( _ServStart, _EndDate, MONTH )

[OPS-MLTSD]

thank you so much! Quick question, let's say if End Eate is NULL then I want the end date to be the last Friday (example if today is Aug 26, Tuesday and I want the end date to be Aug 20, which was the last friday) then can I do something like this?

Duration = 
    VAR _ServStart = Service[Service Start Date]
    VAR _ServEnd = Service[Service End Date]
    VAR _LastRefresh = today () - weekday(today(),16)))
    VAR _EndDate = IF ( ISBLANK ( _ServEnd ), _LastRefresh, _ServEnd )
RETURN 
    DATEDIFF ( _ServStart, _EndDate, MONTH )