Forum Discussion

samoberoi's avatar
samoberoi
Icon for Helper III rankHelper 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
samoberoi's avatar
samoberoi
Icon for Helper III rankHelper 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
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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
    Icon for Helper III rankHelper 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
        Icon for Helper III rankHelper 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