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

    • samoberoi's avatar
      samoberoi
      Helper III
      2 years ago

       

       

      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
      AA-BB-111 6000

       

       

      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.