Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Did you use a disconnected table?
Proud to be a Super User!
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.
Proud to be a Super User!
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
Did you use a disconnected table?
Proud to be a Super User!
@BachFel i would create a flag for the working days, ie a new column calls isworkingday = 1
and then use that in your calculation
Proud to be a Super User!
can u please show an example?
@BachFel do you have a date table?
Proud to be a Super User!
@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())
Proud to be a Super User!
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
@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 () )
)
Proud to be a Super User!
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...
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.
Proud to be a Super User!
it looks like the relationship has been created, what error you getting?
Proud to be a Super User!
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..
@BachFel show me how you adjusted the formula?
Proud to be a Super User!
Tage_seit_erster_Objektbegung =
IF (
ISBLANK (KontElemente[Datum der Besichtigung]);
BLANK ();
IF(Datumstabelle[AlleDaten]) = 1; DATEDIFF (KontElemente[Datum der Besichtigung]; TODAY (); DAY ); BLANK () )
@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
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |