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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.