Forum Discussion

samoberoi's avatar
samoberoi
Helper III
2 years ago
Solved

Network days from a certain date

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 dat...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

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's avatar
samoberoi
Helper III
2 years ago

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

IDAVAILABLE FROM DATEAVAILABLE TO DATE
AA-BB-12309/04/2022 
AA-BB-124 06/04/2024
AA-BC-125  
AC-BC-12311/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 16232023
AA-BC-125 22232023
AA-BB-124 5000
AC-BC-123 001423
CA-BB-125 22500
AD-BD-123 22232023

 

 

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
    • samoberoi's avatar
      samoberoi
      Helper III
      2 years ago

      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's avatar
        lbendlin
        Super User
        2 years ago
        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.