DAX Help - calculating days

[Anonymous]

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

[Anonymous]

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

[v-yuta-msft · Accepted solution]

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