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

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

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'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.

      Thanks

    • 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