DAX Help - Number of working days to be fixed

[vinaydavid]

Hi,

A part of my scenario is explained in the below thread.

https://community.powerbi.com/t5/Desktop/Filter-data-using-date-range-from-slicer/td-p/483072

I was able to get the number of working days working in a date slicer (between).

However, I am struck in a situation where I need the VAR __ACT_DAYS to be fixed across the calculation.

Here is the code which I have used to calculate the % share of utilization of a team across different projects under a supervisor.

Measure =
VAR __END =
    MAX ( Query1[Date] )
VAR __START =
    MIN ( Query1[Date] )
VAR __DAYS =
    COUNTROWS (
        FILTER (
            Dates_Table,
            Dates_Table[Workday] = 1
                && Dates_Table[Date] <= __END
                && Dates_Table[Date] >= __START
        )
    )
VAR __ACT_DAYS =
    IF ( WEEKDAY ( __START ) = 1 || WEEKDAY ( __START ) = 7, __DAYS, __DAYS + 1 )
VAR __RESULT =
    DIVIDE (
        CALCULATE (
            SUM ( Query1[Hours Logged] ),
            ALLEXCEPT ( Query1, Query1[Supervisor], Query1[Project] ),
            FILTER ( Query1, Query1[Date] <= __END && Query1[Date] >= __START ),
            Query1[Work Log Category] <> "Leave"
        ),
        CALCULATE (
            DISTINCTCOUNT ( Query1[Employee] ),
            ALLEXCEPT ( Query1, Query1[Supervisor] ),
            FILTER ( Query1, Query1[Date] <= __END && Query1[Date] >= __START )
        ) * ( __ACT_DAYS * 7.8 ) - [Leaves_Measure],
        0
    )
RETURN
    IF ( __RESULT > 0, __RESULT, 0 )

Problem description:

I want the __ACT_DAYS variable value to be fixed in the denominator. So that I can show the % share of the utilization (at Supervisor level - say 45%) across different projects (Say Project A-30%, B-9% & C-6% = 45%).

I think, the MAX & MIN functions are picking the dates from individual projects and giving out the net days in each projects. 

(For the above code, the values for __ACT_DAYS is something like 6, 2, & 1 based on dates of projects)

Background: I have 2 tables, 1 with supervisor (includes hours, dates, projects etc.,) another table is a Date Table to find count of working days. No relation exists between these tables.

Thanks for your help in advance!

Regards,

Dav

[v-shex-msft]

HI @vinaydavid,

>>I think, the MAX & MIN functions are picking the dates from individual projects and giving out the net days in each projects. 

As you said, your tables do not contain any relationships. Obviously, your date table also not been interact or filter by current row contents projects. It should extract the min/max date across all records instead of grouped by each project.

>>I want the __ACT_DAYS variable value to be fixed in the denominator. So that I can show the % share of the utilization (at Supervisor level - say 45%) across different projects (Say Project A-30%, B-9% & C-6% = 45%).

How did these rates percent mapping?  Can you please explain more about these?

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng