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.
Hi, I am computing the difference between two working dates to know the lenght of days used, the formula is below;
TAT = CALCULATE(SUM(CalendarTable[WorkingDay]),
DATESBETWEEN(CalendarTable[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair])
)
WorkingDay = SWITCH(CalendarTable[WeekNo],1,0,7,0,1)
But i realized that i forgot to add the holidays in the equation of the working day, on top of that, i have difficulty because my data is across different countries that does not share the same holidays.
I shared the data below.
https://drive.google.com/open?id=1RmBqHxvPOYZ-wTEplvaOlln_ajjnnKP8
Thanks in advance for the help.
I have a column for the country in each row so it can be used to filter the TAT computation based on the country holiday list, I guess.
I have been at it for hours and can't seem to figure out how to do it. Any help will be appreciated.
Solved! Go to Solution.
Hi@Anonymous
For this use case, you need to calculate first the number of weekdays within your specified date range then deduct the number of weekdays that are holidays. Here's what I've come up with:
NETWORKDAYS W/ HOLIDAYS = VAR HOLIDAYS_ = CALCULATE ( SUM ( Holidays[Is Weekday?] ), DATESBETWEEN ( Holidays[Date], 'Processed Data'[Date of failure], 'Processed Data'[Date of repair] ), Holidays[Country] = EARLIER ( 'Processed Data'[Country] ) ) VAR REGuLAR_NETWORKKDAYS_ = CALCULATE ( SUM ( Dates[Is Weekday?] ), DATESBETWEEN ( Dates[Date], 'Processed Data'[Date of failure], 'Processed Data'[Date of repair] ) ) RETURN REGULAR_NETWORKKDAYS_ - HOLIDAYS_
Please note that:
Also, you can create a date calendar in DAX or M without pulling in data from an externa source. Here are sample codes:
M -
let Source = List.Dates(#date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2018,1,1)) +1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text), #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Is Weekday?", each if [Day Name]="Saturday" or [Day Name]="Sunday" then 0 else 1, Int64.Type) in #"Added Custom"
DAX
CALENDAR Table = CALENDAR ( MIN ( 'Processed Data'[Date of failure] ), MAX ( 'Processed Data'[Date of repair] ) )
Proud to be a Super User!
Hi@Anonymous
For this use case, you need to calculate first the number of weekdays within your specified date range then deduct the number of weekdays that are holidays. Here's what I've come up with:
NETWORKDAYS W/ HOLIDAYS = VAR HOLIDAYS_ = CALCULATE ( SUM ( Holidays[Is Weekday?] ), DATESBETWEEN ( Holidays[Date], 'Processed Data'[Date of failure], 'Processed Data'[Date of repair] ), Holidays[Country] = EARLIER ( 'Processed Data'[Country] ) ) VAR REGuLAR_NETWORKKDAYS_ = CALCULATE ( SUM ( Dates[Is Weekday?] ), DATESBETWEEN ( Dates[Date], 'Processed Data'[Date of failure], 'Processed Data'[Date of repair] ) ) RETURN REGULAR_NETWORKKDAYS_ - HOLIDAYS_
Please note that:
Also, you can create a date calendar in DAX or M without pulling in data from an externa source. Here are sample codes:
M -
let Source = List.Dates(#date(2018, 1, 1), Number.From(DateTime.Date(DateTime.LocalNow()) - #date(2018,1,1)) +1, #duration(1,0,0,0)), #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error), #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Date", type date}}), #"Inserted Day Name" = Table.AddColumn(#"Changed Type", "Day Name", each Date.DayOfWeekName([Date]), type text), #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Is Weekday?", each if [Day Name]="Saturday" or [Day Name]="Sunday" then 0 else 1, Int64.Type) in #"Added Custom"
DAX
CALENDAR Table = CALENDAR ( MIN ( 'Processed Data'[Date of failure] ), MAX ( 'Processed Data'[Date of repair] ) )
Proud to be a Super User!
Dear @danextian ,
Plese help me to get out of this !!!
I want Values Row by row in Calculated column followed same way as you mentioned below and it is not giving me negtive values,Please find the Below Data whichi have and Screen Captures.
Thanks In Advance.
Dax Formula What I used:
Please post a sample data that is not screen caps and a sample table of your expected result.
Proud to be a Super User!
Invoice Date | Posted Date | Payment Date | Date Due | Type of supplier | Invoice Received on |
13-06-2019 | 31-07-2019 | 06-12-2019 | 13-07-2019 | 3rd Party | 12-07-2019 |
15-04-2019 | 12-08-2019 | 06-12-2019 | 15-05-2019 | 3rd Party | 08-08-2019 |
22-10-2019 | 30-10-2019 | 13-12-2019 | 21-11-2019 | 3rd Party | 29-10-2019 |
24-10-2019 | 30-10-2019 | 13-12-2019 | 23-11-2019 | 3rd Party | 29-10-2019 |
31-10-2019 | 07-11-2019 | 16-12-2019 | 30-11-2019 | 3rd Party | 31-10-2019 |
29-10-2019 | 12-11-2019 | 13-12-2019 | 28-11-2019 | 3rd Party | 06-11-2019 |
31-10-2019 | 28-11-2019 | 06-12-2019 | 30-11-2019 | 3rd Party | 08-11-2019 |
04-11-2019 | 12-11-2019 | 04-12-2019 | 04-12-2019 | 3rd Party | 11-11-2019 |
05-11-2019 | 15-11-2019 | 06-12-2019 | 05-12-2019 | 3rd Party | 11-11-2019 |
31-10-2019 | 28-11-2019 | 06-12-2019 | 30-11-2019 | 3rd Party | 12-11-2019 |
31-10-2019 | 18-11-2019 | 13-12-2019 | 30-11-2019 | 3rd Party | 12-11-2019 |
31-10-2019 | 18-11-2019 | 13-12-2019 | 30-11-2019 | 3rd Party | 12-11-2019 |
01-11-2019 | 18-11-2019 | 30-12-2019 | 01-12-2019 | 3rd Party | 12-11-2019 |
31-10-2019 | 18-11-2019 | 13-12-2019 | 30-11-2019 | 3rd Party | 13-11-2019 |
08-11-2019 | 18-11-2019 | 30-12-2019 | 08-12-2019 | 3rd Party | 14-11-2019 |
28-05-2019 | 28-11-2019 | 06-12-2019 | 27-06-2019 | 3rd Party | 15-11-2019 |
12-11-2019 | 18-11-2019 | 30-12-2019 | 12-12-2019 | 3rd Party | 15-11-2019 |
31-10-2019 | 20-11-2019 | 13-12-2019 | 30-11-2019 | 3rd Party | 18-11-2019 |
01-10-2019 | 20-11-2019 | 13-12-2019 | 31-10-2019 | 3rd Party | 19-11-2019 |
04-10-2019 | 20-11-2019 | 13-12-2019 | 03-11-2019 | 3rd Party | 19-11-2019 |
05-11-2019 | 27-11-2019 | 06-12-2019 | 05-12-2019 | 3rd Party | 21-11-2019 |
06-11-2019 | 27-11-2019 | 06-12-2019 | 06-12-2019 | 3rd Party | 21-11-2019 |
09-11-2019 | 27-11-2019 | 06-12-2019 | 09-12-2019 | 3rd Party | 21-11-2019 |
14-11-2019 | 27-11-2019 | 06-12-2019 | 14-12-2019 | 3rd Party | 21-11-2019 |
15-10-2019 | 27-11-2019 | 12-12-2019 | 14-11-2019 | 3rd Party | 21-11-2019 |
01-12-2019 | 01-12-2019 | 13-12-2019 | 31-12-2019 | 3rd Party | 22-11-2019 |
01-12-2019 | 01-12-2019 | 13-12-2019 | 31-12-2019 | 3rd Party | 22-11-2019 |
The Expected result in Excel:
Invoice Date to Receipt Date (Calender Days ) | Invoice Date to Payment Date (Calender Days ) | Receipt Date to Posting Date ( Working Days ) | Receipt Date to Payment Date (Working Days) | Posting to Payment Date |
-3 | -31306 | 2 | -31305 | -31,307 |
5 | -31300 | 2 | -31305 | -31,307 |
20 | -31284 | 2 | -31305 | -31,307 |
3 | -31298 | 4 | -31301 | -31,305 |
3 | -31298 | 1 | -31301 | -31,302 |
1 | -31298 | 2 | -31299 | -31,301 |
10 | -31289 | 2 | -31299 | -31,301 |
-9 | -31299 | 8 | -31292 | -31,300 |
13 | -31284 | 1 | -31298 | -31,299 |
2 | -31293 | 3 | -31295 | -31,298 |
2 | -31293 | 3 | -31295 | -31,298 |
4 | -31291 | 3 | -31295 | -31,298 |
2 | -31289 | 2 | -31292 | -31,294 |
2 | -31285 | 3 | -31287 | -31,290 |
4 | 24 | 265 | 20 | -247 |
0 | 1 | 1 | 1 | - |
18 | 19 | 1 | 1 | - |
125 | 126 | 1 | 1 | - |
168 | 169 | 1 | 1 | - |
8 | 10 | 2 | 2 | - |
12 | 14 | 2 | 2 | - |
57 | 59 | 2 | 2 | - |
0 | 3 | 3 | 3 | - |
3 | 6 | 3 | 3 | - |
4 | 7 | 3 | 3 | - |
10 | 13 | 3 | 3 | - |
15 | 18 | 2 | 3 | 1 |
18 | 21 | 2 | 3 | 1 |
5 | 8 | 1 | 3 | 2 |
10 | 14 | 2 | 4 | 2 |
2 | 9 | 2 | 7 | 5 |
2 | 14 | 6 | 12 | 6 |
122 | 138 | 10 | 16 | 6 |
8 | 27 | 13 | 19 | 6 |
6 | 27 | 15 | 21 | 6 |
0 | 9 | 2 | 9 | 7 |
22 | 32 | 3 | 10 | 7 |
Thanks @danextian. I tried it but i am having error on the earlier statement, my column is not showing on the formula below,
NETWORKDAYS W/ HOLIDAYS =
VAR HOLIDAYS_ =
CALCULATE (
SUM ( Holiday[IsWorkingDay] ),
DATESBETWEEN (
Holiday[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair]
),
Holiday[Country] = EARLIER ( 'Processed Data'[Country] )
)
VAR REGuLAR_NETWORKKDAYS_ =
CALCULATE (
SUM ( CalendarTable[WorkingDay] ),
DATESBETWEEN (
CalendarTable[Date],
'Processed Data'[Date of failure],
'Processed Data'[Date of repair]
)
)
RETURN
REGULAR_NETWORKKDAYS_ - HOLIDAYS_
and I am getting this error, upon enter
A single value for column 'Date of failure' in table 'Processed Data' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.
By the way, this is supposed to be a calculated column and not a measure.
Proud to be a Super User!
Hi @danextian,
Yes, i was adding it as a measure, that is the problem, the solution works flawlessly. Thanks a lot for the help.
You're welcome.
Proud to be a Super User!
Hi danex,
I have the exact same problem as jcatindoy. I tried out your method, it didn't workout. An error occured: A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This is not supported.
Here is my code, could you please take a look at it? Thanks!!
businessday =
var _END =Fact[completedon]
var _START =Fact[createdon]
var _holiday =
CALCULATE (
SUM (Holidays[Isweekday]),
DATESBETWEEN (Holidays[holidaydate],_START,_END),
Holidays[countrycode]=EARLIER(Fact[countrycode])
)
var _workinghours =
SUMX(
CALCULATETABLE(
Dates,
DATESBETWEEN(Dates[Date],_START,_END),
Dates[IsWorkingDay] = TRUE()),
MAX(MIN(Dates[End],_END)-MAX(Dates[Start],_START),0)
)
return IF(_workinghours-_holiday>0,_workinghours-_holiday,BLANK())
Hi @Gabiiiii,
The first argument in DATESBWEEN requires a Date column that doesn't contain duplicates. It is possible that either your Dates[Date] or Holidays[holidaydate] has the duplicates but the culprit is most likely the latter. I would use something like this instead:
VAR __HOLIDAYS =
CALCULATE (
SUM ( Holidays[Weekday Holiday Count] ),
FILTER (
Holidays,
Holidays[Holiday] >= EARLIER ( Data[Start] )
&& Holidays[Holiday] <= EARLIER ( Data[End] )
&& Holidays[Country] = EARLIER ( Data[Country] )
)
)
Please refer to this sample pbix - https://drive.google.com/file/d/1pbYAD2KZf-RFq2kf3vvmI0IcxHvwmoIg/view?usp=sharing
Proud to be a Super User!
Hi @danextian,
Thank you very much for sharing this wonderful solution.
for same requiremnt im using this and the only diffrence is Holiday is maintaining at "ATC" level instead of "Countries" where ATC is unique same Country. and ATC coulmn in EMPLOYEE table Nither Calculated column Nor Measure, it is a regular column appear in Model.
while creating the measure got the similar issues mentioned by @Anonymous as below :
"Error: Calculation error in measure 'EMPLOYEE'[NETWORKDAYS W/ HOLIDAYS]: A single value for column 'FROM_DATE' in table 'EMPLOYEE' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
Code written was as below :
NETWORKDAYS W/ HOLIDAYS =:
VAR HOLIDAYS_ =
CALCULATE (
SUM ( HOLIDAY[ISWORKINGDAY] ),
DATESBETWEEN (
HOLIDAY[HOLIDAY],
EMPLOYEE[FROM_DATE],
EMPLOYEE[TO_DATE]
),
HOLIDAY[ATC] = EARLIER ( EMPLOYEE[ATC] )
)
VAR REGuLAR_NETWORKKDAYS_ =
CALCULATE (
SUM ( CALANDER(ISWORKINGDAY) ),
DATESBETWEEN (
CALANDER[Date],
EMPLOYEE[FROM_DATE],
EMPLOYEE[TO_DATE]
)
)
RETURN
REGULAR_NETWORKKDAYS_ - HOLIDAYS_
could you please help me to undestand what went wrong.
Hi @danextian,
after downloading your .pbix file i realised the mistake had done, and Now code is working as expectd.
Thank and Regards,
Dinesh
Mmm. That's weird. I just copied the formula from my working PBIX. Have you checked on the PBIX by the way?
Proud to be a Super User!
...and the PBIX
https://drive.google.com/open?id=126_RK2j9R-Gu_n7Nwh2SWouNe-pOTqdC
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 |
---|---|
105 | |
97 | |
80 | |
67 | |
63 |
User | Count |
---|---|
146 | |
110 | |
107 | |
86 | |
63 |