Network days from a certain date

[samoberoi]

Hi All,

I have a query on some work which has previously been done on Excel and now it has to be done on Power BI. It is just to calculate the number of network days from a joining or leaving dates of some resources.
In simple terms, the way it should work as e.g. if there is no date in  FROM_DATE and  TO_DATE columns in Table A or if these are blanks, then it should give the normal Network days e.g for April it should show Network days as 22 and for May it should show 23 and so on for the resources where these two date columns are blank, but for the resourceID's where they have the dates in either the  FROM_DATE and  TO_DATE columns in Table A, then it should calculate the Network days after the  FROM_DATE or before the  TO_DATE for that resource for that specific month e.g. Let's say a resource's  TO_DATE is showing 26th June 2024 and FROM_DATE column is showing blank for that particular resource , so it should calculate the Network days for June month starting from the first of June month till 26th June 2024 which will be 18 Network days. Similarly e.g if the resource's FROM_DATE is 26th June 2024 and TO_DATE column is showing blank for that particular resource, then it should calculate Network days from 26th June 2024 till the end of June month, which will be only 3 Network days.

Help will be much appreciated.

Thanks & regards

[lbendlin · Accepted solution]

[lbendlin]

Add a column to the Dates table in your data model that clearly identifies the working days for your scenario  (by region/country etc)

[samoberoi]

Hi Ibendlin,

Normal network days is working fine, but what if let's say a resource's  TO_DATE is showing 26th June 2024 and FROM_DATE column is showing blank for that particular resource , so it should calculate the Network days for June month starting from the first of June month till 26th June 2024 which will be 18 Network days. Similarly e.g if the resource's FROM_DATE is 26th June 2024 and TO_DATE column is showing blank for that particular resource, then it should calculate Network days from 26th June 2024 till the end of June month, which will be only 3 Network days. It doesn't filter number of capacity in days for a few specific resource.

Thanks

[lbendlin]

What is your definition of weekend?  Are there any holidays in the affected period?

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[samoberoi]

Hi Ibendlin, 

The weekend should just be counted as Saturday & Sunday. No other holidays are necessary to be counted. Please find the sample data as well as expected results below.

TABLE A

ID	AVAILABLE FROM DATE	AVAILABLE TO DATE
AA-BB-123	09/04/2022
AA-BB-124		06/04/2024
AA-BC-125
AC-BC-123	11/06/2024
CA-BB-125		07/05/2024
AD-BD-123

EXPECTED RESULTS

		April capacity (Days)	May capacity (Days)	June capacity (Days)	July capacity (Days)
AA-BB-123		16	23	20	23
AA-BC-125		22	23	20	23
AA-BB-124		5	0	0	0
AC-BC-123		0	0	14	23
CA-BB-125		22	5	0	0
AD-BD-123		22	23	20	23

Meanwhile i tried to use the following piece of DAX, which obviously doesn't work.

Working Days =

VAR ColDate = RELATED('DateTable'[Date])

VAR earliestfrom = Table_A[AvailableFromDate]

VAR latesto = Table_A[AvailableToDate]

RETURN

 IF( ColDate > earliestfrom,

 CALCULATE(

 SUM('DateTable'["Is Work Day"]),

 ALL('Date'),

'DateTable'[Date] <= ColDate && 'DateTable'[Date] > earliestfrom

 ),

 IF( ColDate < latesto,

 CALCULATE(

 SUM('DateTable'["Is Work Day"]),

 ALL('DateTable'),

'DateTable'[Date] >= ColDate && 'DateTable'[Date] < latesto

 )

Please let me know if it needs some tweaking or i need a completely different DAX to get the expected results.

Thanks

[lbendlin · Accepted solution]

[samoberoi]

Hi Ibendlin,

Thank you for your response and for your effort in helping me on it. It seems to be working fine as per what i requested. There are just a couple of things i am facing the problem with. Firstly, when i put this measure in the table visual along with other field values, it comes up with the error as below; but when i use the ID from the Table A and months from the Date table in the slicer, it works fine showing the filtered values for each resource for each month. I think it is because there are the dates in my data table prior to 01/01/2024 as well e.g in Table A here and the dates in your DAX start from 2024/01/01.

Secondly, i apologise. I forgot to mention. There should have been one more condition in it saying if there are dates in both  AVAILABLE FROM DATE & AVAILABLE TO DATE and if the monthchecked firstly from AVAILABLE TO DATE column matches with the month from the Date Table, then calculate the Network days up untill that date in AVAILABLE TO DATE e.g. as in the example highlighted in red below if i filter by April in slicer, then it should count the Network days as 6 as per the date 08/04/2024 from the AVAILABLE TO DATE otherwise should take second date value from AVAILABLE FROM DATE.

TABLE A

ID	AVAILABLE FROM DATE	AVAILABLE TO DATE
AA-BB-123	09/04/2022
AA-BB-124		06/04/2024
AA-BC-125
AC-BC-123	11/06/2024
CA-BB-125		07/05/2024
AD-BD-123
AA-BB-111	15/02/2023	08/04/2024

Hope it will come out with some resolution.

Thanks again for your help.

Regards

[lbendlin]

when i put this measure in the table visual along with other field values, it comes up with the error as below

In my COALESCE formulas you can replace the hardcoded dates with your min and max fact dates.

 if i filter by April in slicer

Filter? Slicer?  That was not mentioned as a requirement.  Your slicer would need to be fed by a disconnected table.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[samoberoi]

EXPECTED RESULTS

		April capacity (Days)	May capacity (Days)	June capacity (Days)	July capacity (Days)
AA-BB-123		16	23	20	23
AA-BC-125		22	23	20	23
AA-BB-124		5	0	0	0
AC-BC-123		0	0	14	23
CA-BB-125		22	5	0	0
AD-BD-123		22	23	20	23
AA-BB-111		6	0	0	0

SECOND ISSUE

1. In my COALESCE formulas you can replace the hardcoded dates with your min and max fact dates.

Related to the second issue, i tried to use Min & Max for the dates from the fact table Table A in this example, but it still doesn't work.

Hope i could be explain it better and could be provide better sample data.

Thanks again.

[lbendlin · Accepted solution]

see attached

[samoberoi]

Hi Ibendlin,

Can't thank you enough for your help. Meanwhile, please recommend any books or online platform for Advanced DAX.

Thanks again and stay blessed!

[lbendlin]

https://www.amazon.com/Definitive-Guide-DAX-intelligence-Microsoft/dp/1509306978

[samoberoi]

Hi Ibendlin, 

The weekend should just be counted as Saturday & Sunday. No other holidays are necessary to be counted.

Thanks