Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

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.



Helper I
Helper I



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.






Helper I
Helper I


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.

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]))


Weekday = WEEKDAY([Date],2)


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

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



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] ) )  )
    IF ( OR ( SELECTEDVALUE ( 'Date'[Weekday] ) = 6,
              SELECTEDVALUE ( 'Date'[Weekday] ) = 7 ),
        DIVIDE ( _neednumber, _countdays )



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

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors