Difference between today and given date in Working Days

[Kopek]

Hello All,

I tried to serch for it, but have not found anything.

I would like to calculate difference between today date and estimated go live date in working days.

Usually estimated go live is grater than today, so then measure should shows 0.

I have created calendar in DAX, have marked all working days as 1, all weekends as 0, i have done relationship beetween calendar and column with data. I also tried to create measure, but withous success.

Could you please help me ?

I am attaching screenshots for your reference.

[v-jiascu-msft]

Hi @Kopek,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Kopek]

Hello, Sorry for delay, I was on sick leave.

And finally to calculate it I used Query function - not measure.

But I think that above measure will work fine.

Thank you everyone for your help!

Regards:)

[v-jiascu-msft · Accepted solution]

Hi @Kopek,

Try this formula as a calculated column, please.

column =
IF (
    [Estimated Go-Live] >= [Today],
    0,
    CALCULATE (
        SUM ( 'Calendar'[Weekend vs. Workday] ),
        FILTER (
            'Calendar',
            'Calendar'[Date] >= [Estimated Go-Live]
                && 'Calendar'[Date] <= [Today]
        ),
        ALL ( Table1 )
    )
)

Best Regards,
Dale

[Kopek]

Hi All - I have found solution using power query, and I have already counted it, but may you let me know if there is a solution using DAX measure ?

Thanks!

[Anonymous]

The function you are looking for is DATEDIFF.

=DATEDIFF(Provide date1 column, provide date 2 column, DAY)

This returns the difference in days.

[Kopek]

Hi Nikhilmekala,
Thanks for that, but I already tried it, and this function does not work if today (date 1) is greater than end date ( date 2).
Also I need difference in working days (excluding weekends) , all days

[Anonymous]

You can put today as date2 and enddate at date1, that would get rid of the minus sign.

To get the count of working days only (no Saturday and Sunday), the following may give you some ideas to improvise:

In order to get the number of weekdays between date1 and date2, you first have to count the number of days (weekends+weekdays) between date1 and date2...

..and then from this count, subtract the number of Saturdays and Sundays between date1 and date2...

You can use the =WEEKDAY() function to determine what day any given date was. Weekday will return a number, for example, 1 for Sunday, 2 for Monday....and 7 for Saturday. You can write a formula to count 1's and 7's between date1 and date2 to arrive at the number of the Saturdays and Sundays between date1 and date2.

You will no doubt, have to create a seperate date table to achieve all this.