Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
x-File
Helper I
Helper I

Spread value evenly over given period excluding weekends

Hi all,

 

I'm pretty OK with DAX but not that good. I've seen multiple posts here which come close and I've tried their solutions, but it did not work.

 

I need to calculate the amount of people we need to recruit per day to make sure we will find enough people for our next project.

 

So project X group 1 for example, starts at 01/04/2023, has a recruitment window of 10 days (ending on Mar 31/03/2023, so 10 days minus 1), and recruitment can not take place in the weekend.

 

There's 8 working days between start recruitment and start project, so that's 100/8 = roughly 12 people per day to recruit.

 

So basically, I've got

a date table

and a recruitment table with people needed, recruitment window, recruitment start date and project start date

 

Could somebody pleae help me? I know I might end up with half numbers and stuff but that's OK.

 

Thanks in advance!Example recruitment.JPG

4 REPLIES 4
x-File
Helper I
Helper I

Hi @v-zhangti ,

Could you please again try to help me out? I still haven't found out how to solve this. To add, I can't share files here (I don't know why), but I could share them personlly by mail?

thanks in advance, I'd be really thankful if you could help me out.

Rgds,


Felix

x-File
Helper I
Helper I

Hi,

 

Thanks so much for the answer, but I'm afraid it covers only part of the solution: I want actually to have all groups in once, not separate between groups 1 and 2, but I want to see both together in the same graph.

 

Could you, @v-zhangti , or somebdoy else, please help me out? I'm willing to send the actual file instead of this example file if it helps.

 

Thanks!

 

Rgds,


Felix

x-File
Helper I
Helper I

Hi, 

It did not work exactly, the measures didn't give errors but they didn't shows as I would like... could I send you my current files (both the Excel behind and the actual Pbix)? I'm afraid I can not resolve it otherwise.

v-zhangti
Community Support
Community Support

Hi, @x-File 

 

You can try the following methods.
New table:

Date = CALENDAR(MIN('Table'[Recruitment start]),MAX('Table'[Project start]))

Column:

Weekday = WEEKDAY([Date],2)

Measure:

Measure =
IF ( SELECTEDVALUE ( 'Table'[Project start] ) > SELECTEDVALUE ( 'Date'[Date] )
        && SELECTEDVALUE ( 'Table'[Recruitment start] ) <= SELECTEDVALUE ( 'Date'[Date] ),
    1,
    0
)

Put measure in the date matrix view and set equal to 1.

vzhangti_0-1675752824218.png

Measure:

Number = 
VAR _neednumber = SUM ( 'Table'[Needed] )
VAR _countdays =
    SELECTEDVALUE ( 'Table'[Recruitment window] )
        - CALCULATE ( COUNT ( 'Date'[Date] ),
            FILTER ( ALL ( 'Date' ),
                OR ( [Weekday] = 6, [Weekday] = 7 )
                    && [Date] < SELECTEDVALUE ( 'Table'[Project start] )
                    && [Date] >= SELECTEDVALUE ( 'Table'[Recruitment start] ) )  )
RETURN
    IF ( OR ( SELECTEDVALUE ( 'Date'[Weekday] ) = 6,
              SELECTEDVALUE ( 'Date'[Weekday] ) = 7 ),
        0,
        DIVIDE ( _neednumber, _countdays )
    )

Result:

vzhangti_1-1675752862050.pngvzhangti_2-1675752874191.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors