datedif Function, only for working days (mon-Fri)

[BachFel]

Hello,

follwing problem.

I want to create a DAX formula which shows the difference from a specific date to today.

Tage_seit_erster_Objektbegung = if(KontElemente[Datum der Besichtigung]<>BLANK();TODAY()-KontElemente[Datum der Besichtigung].[Date];BLANK())

Is it possible to count only the working days (monday-Friday).

Thanks in advance

[danextian]

Hi @BachFel,

I would follow the following approach. 

First, I would create a disconnected calendar table ( a table that has no relationship with your Fact table) either in DAX or in M which contains all the dates that my fact table has. The calendar table would indicate 1 if a day is a weekday and 0 if otherwise. Here's my DAX formula:

Calendar (Disconnected) =
VAR START_DATE_ =
    DATE ( 2018, 3, 1 )
VAR END_DATE_ =
    DATE ( 2018, 4, 20 )
VAR DATES_ =
    CALENDAR ( START_DATE_, END_DATE_ )
RETURN
    ADDCOLUMNS (
        DATES_,
        "Name of Day", FORMAT ( [Date], "ddd" ),
        "Is Weekday?", IF (
            FORMAT ( [Date], "ddd" ) = "Sat"
                || FORMAT ( [Date], "ddd" ) = "Sun",
            0,
            1
        )
    )

You may edit the START_DATE_ and END_DATE_ variables above as desired.

In my fact table, I would create a calculated column that sums the value in Is Weekday? column fromt the disconnected calendar table filtered by a specific date till today. Here's my DAX formula: 

Workday Difference =
    CALCULATE (
        SUM ( 'Calendar (Disconnected)'[Is Weekday?] ),
        DATESBETWEEN ( 'Calendar (Disconnected)'[Date], 'Fact'[Date], TODAY () )
    )
        - 1

Notice that added -1 after the latest parenthesis. This is  because the sum that is being returned is the sum from start to end dates and not the difference between the two.

[BachFel]

Hi @danextian,

i created a calender table where weekdays have a 1 and sat/ sun has a 0. This column in is the table called:

Datumstabelle[IsWorkingDay]

then I added your second formula. But the result is wrong.

I´m not sure about: DATESBETWEEN (Datumstabelle[Daten]. This is the column in my calender with all possible dates.

I´ve no clue where the mistake could be 

[danextian · Accepted solution]

Did you use a disconnected table?

[vanessafvg]

@BachFel i would create a flag for the working days, ie a new column calls isworkingday = 1

and then use that in your calculation

[BachFel]

can u please show an example?

[vanessafvg]

@BachFel  do you have a date table?   

[BachFel]

@vanessafvg I just created one. What shall I do now

https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-step...

That´s how I did it

[vanessafvg]

@BachFel there is possibly a better way to do this but what i would do is then create a  day of week column

Date =
ADDCOLUMNS (
CALENDAR(2015-01-01,2018-01-01),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Day of Week", FORMAT([Date],"dddd"))

then create calculated column that is called IsWorkingDay ( you could do a an if statement too)

IsWorkingDay=
SWITCH (
TRUE (),
'Date'[Day of Week] = "Saturday", 0,
'Date'[Day of Week] = "Sunday", 0,
1
)

then you will need to add the isworkingday to your calculation, not sure what you are working out below, is there a reason you not using the datediff function?

Tage_seit_erster_Objektbegung = if(KontElemente[Datum der Besichtigung]<>BLANK();TODAY()-KontElemente[Datum der Besichtigung].[Date];BLANK())

[BachFel]

Thank u so far!

There is no reason for not using the datediff function, I just didn´t know that it´s possible in DAX.

---

then you will need to add the isworkingday to your calculation, not sure what you are working out below, is there a reason you not using the datediff function?

Tage_seit_erster_Objektbegung = if(KontElemente[Datum der Besichtigung]<>BLANK();TODAY()-KontElemente[Datum der Besichtigung].[Date];BLANK())

---

I´m trying to show the difference in days between the [Datum der Besichtigung] and today (but only the weekdays). If [Datum der Besichtigung] is empty there should be no calculation. I´m not really sure how to install the isworkingday in the formula..

Would be grateful if u can help me out 

[vanessafvg]

@BachFel not sure if i have captured what you have asked for but give it a go

assuming you have created a date table which is what is required and then created a relationship on that date to the date in your table

Tage_seit_erster_Objektbegung =
IF (
    ISBLANK ( datetable[date] ),
    BLANK (),
    IF ( isworkingday = 1, DATEDIFF ( datetable[date], TODAY (), DAY ), BLANK () )
)

[BachFel]

@vanessafvg

Tage_seit_erster_Objektbegung =
IF (
    ISBLANK (KontElemente[Datum der Besichtigung]);
    BLANK ();
    IF (Datumstabelle[AlleDaten]) = 1; DATEDIFF (KontElemente[Datum der Besichtigung]; TODAY (); DAY ); BLANK () )

I´m not able to select the table Datumstabelle where the row AlleDaten is in...

I created a relationship as u mentioned but it won´t work.. I´m lost...

[danextian]

Hi @BachFel

If you created  a relationship between the sum date table and your fact table, the formula will not work as the result gets filtered. Please delete the relationship and check on the  result again.

[vanessafvg]

@BachFel

it looks like the relationship has been created, what error you getting?

[BachFel]

That there are to less arguments for using the if-function.

This is caused by the reason that I´m not able to find the table Datumstabelle I guess..

[vanessafvg]

@BachFel show me how you adjusted the formula?

[BachFel]

Tage_seit_erster_Objektbegung =
IF (
    ISBLANK (KontElemente[Datum der Besichtigung]);
    BLANK ();
    IF(Datumstabelle[AlleDaten]) = 1; DATEDIFF (KontElemente[Datum der Besichtigung]; TODAY (); DAY ); BLANK () )

[vanessafvg]

@BachFel i meant the formula i gave you?  have  you adjusted it to your model?  secondly you need to use the date table not the other table