Counting Workdays to be used in a Calculation on another table

[kleighton]

Hi!

-I'm calculating Capacity% of a resource, based on 5 day week (including holidays) vs 7 day week * Resource Hrs/day.

I'm using a  calendar table and calculated column for workday:

 -Workday = IF(ISBLANK(Calender[Holiday]),IF(Calender[DayOfWeekNumber]<=5,1,0),0)

I have a measure to count these workdays per month: 

 -Workdays = CALCULATE(COUNT(Calender[Workday]),Calender[Workday]=1)

These are all working fine...

My data table; 

TotalDaysMonth = COUNTROWS ( PARALLELPERIOD ( Calender[Date], 0, MONTH))

7days% = DIVIDE ( (Query1[SUM(LOAD1)]), (RELATED (Resource[Hrs/Day]) * Query1[TotalDaysMonth]),0 )

So far, everything works fine, But when i try to calculate 5 days% as follows:

5days% = DIVIDE ( (Query1[SUM(LOAD1)]), (RELATED (Resource[Hrs/Day]) * [Workdays]),0 )

Where [Workdays] is my measure above:

It is returning 1 for the value of the [Workdays] measure and not the count for the Month..

There is a relationship between the tables, but how do i make the measure count the numbers of workdays for that row (Month)?

I guess i'm missing something simple but i can't think of it..

[v-gizhi-msft]

Hi,

Could you please create and share some simple sample and expected result as screenshots with me?

And it can let me help you further and more efficiently.

Remember removing any sensitive data.

Expect your reply!

Best Regards,

Giotto

[kleighton]

Hi,

Thanks everyone for the posts, unfortunately it hasn't helped. below is a recreated sample of my file in its original state, before the various modifications suggested above and without private data sources. 

https://www.dropbox.com/s/e7nbm1fjjb2q2sj/ResCapactiySample.pbix?dl=0

 The 5day% calculation is the problem, Expected result sample as follows:

5days% for Res5 on October 2020 =

(Query1[SUM(LOAD1) 235.66 / ((Resource[Hrs/Day]) 21 * [Workdays] 22)= 51%

p.s I also noticed i had Calendar mistyped as Calender 🙂 now corrected..

Thanks everyone for their input

Keith

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Create two columns in Calendar and Query1 tables:

Year&Month = FORMAT('Calendar'[Date],"YYYY-MM")

Year&Month = FORMAT('Query1'[StartingDate],"YYYY-MM")

2)Try to create this column in Calendar table:

WorkDays per Month =
CALCULATE (
    COUNT ( Calendar[Workday] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        'Calendar'[YearMonthnumber] = EARLIER ( 'Calendar'[YearMonthnumber] )
            && Calendar[Workday] = 1
    )
)

3)Try to create this column in Query1 table:

WorkDays per Month = RELATED('Calendar'[WorkDays per Month])

4)Try this column to show the 5days% expected result:

5days% = DIVIDE ( (Query1[SUM(LOAD1)]), (RELATED (Resource[Hrs/Day]) * Query1[WorkDays per Month]),0 )

5)The result shows:

Here is the changed pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto

[kleighton]

Perfect, Thankyou Giotto!!! 

[v-gizhi-msft]

Hi,

Please try this measure:

5days% =
DIVIDE (
    ( Query1[SUM(LOAD1)] ),
    (
        RELATED ( Resource[Hrs/Day] )
            * CALCULATE (
                [Workdays],
                FILTER (
                    ALLSELECTED ( Calender ),
                    Calender[Date].[Month] IN FILTERS ( Calender[Date].[Month] )
                )
            )
    ),
    0
)

If you still have any issue, please share more info about your Resource table such as [Hrs/Day] data and these three tables relationships(including link columns) as screenshots. And these can let me help you further.

Expect your reply!

Best Regards,

Giotto

[parry2k]

@kleighton change measure for workday in the divide function like this, it will give you workdays across the full month for each month

CALCULATE ( SUM ( 'Calendar'[Workday] ),  ALLSELECTED ( 'Calendar'[Date] ) ) 

[amitchandak]

Can you share sample data and sample output.

[Greg_Deckler]

First, sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, I generally have more luck with RELATEDTABLE vs. RELATED.