Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Help - calculating days

Hi All,

 

I have a Power BI that I am creating for our ticketing system. The goal is to take the number of days that our employees have entered on all their tasks and add it to today to get a "Next Available Date" so that managers know who is available to work. I can do the calculation by itself but when I add it to a table it explodes everything out granularity wise. Any ideas?

 

Here is the DAX calc - 

Next Available Date = CALCULATE(IF(WEEKDAY(NOW()+SUM('Resource Availability'[Remaining Planned Days]),1)=1,NOW()+SUM('Resource Availability'[Remaining Planned Days])+1,IF(WEEKDAY(NOW()+SUM('Resource Availability'[Remaining Planned Days]),1)=7,NOW()+SUM('Resource Availability'[Remaining Planned Days])+2,NOW()+SUM('Resource Availability'[Remaining Planned Days]))))
 
Here is what the data model looks like -
 
Just to recap, If I just put the Team and Users in a table, it filters correctly. I pick a team and only see those users. Once I add the DAX calc it explodes it out and I see all users regardless of the team selected.
 
Any ideas?
 
Thanks,
James
Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    Modify your measure using dax below and check if it can meet your requirement.

    Next Available Date =
CALCULATE (
    IF (
        WEEKDAY ( NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ), 1 ) = 1,
        NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ) + 1,
        IF (
            WEEKDAY ( NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ), 1 ) = 7,
            NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ) + 2,
            NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] )
        )
    ),
    ALLSELECTED ( Teams )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Anonymous ,

     

    Modify your measure using dax below and check if it can meet your requirement.

    Next Available Date =
CALCULATE (
    IF (
        WEEKDAY ( NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ), 1 ) = 1,
        NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ) + 1,
        IF (
            WEEKDAY ( NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ), 1 ) = 7,
            NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] ) + 2,
            NOW () + SUM ( 'Resource Availability'[Remaining Planned Days] )
        )
    ),
    ALLSELECTED ( Teams )
)

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Is it possible to import the resource availabiliy data from MS Projects and import into the Power BI model and create a Gantt chart visualization which displays an overview of what tasks/projects are assigned to people and gives an overview where people have available hours (as gaps in the chart), for the Project Managers to assign new projects/tasks to the available resources/people.

     

    I hope someone understands what I am trying to achieve. ğŸ˜…