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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Labraham36
Helper I
Helper I

Problems with sum of business days

Hello Community!

 

I have a question: On the one hand I have the data of a date. On the other hand, I have a number of days, and what I need is to obtain the day that results from adding that date to that number of days. The problem with this is that that number of days correspond to business days, so in that sum, neither Saturdays, nor Sundays, nor holidays should be taken into account. I have a calendar table with all the possible dates and a field that informs if that date is a business day (weekday not a holiday) with a "True", or if it is a non-business day (Saturday, Sunday or holiday) How could I do to solve this problem?

 

Thank you very much!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Labraham36 ,

I have modify the measure. Please refer to it to see if it helps you.

Create a flag column on calendr table.

flag =
VAR _workday =
    WEEKDAY ( 'Table 2'[Date], 2 )
RETURN
    IF ( _workday > 5, FALSE (), TRUE () )

Then create a measure.

Measure_4 =
VAR _plus =
    MAX ( 'Table'[date] ) + MAX ( 'Table'[value] )
VAR _days =
    CALCULATE (
        COUNT ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[Date] >= MAX ( 'Table'[date] )
                && 'Table 2'[Date] <= _plus
                && 'Table 2'[flag] = FALSE ()
        )
    )
VAR _retyrnda = _plus + _days
VAR _flagresult =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _retyrnda )
    )
VAR _measure1 = _retyrnda + 1
VAR _res =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _measure1 )
    )
VAR _measure2 = _retyrnda + 2
RETURN
    IF (
        _flagresult = FALSE ()
            && _res = TRUE (),
        _measure1,
        IF (
            _flagresult = FALSE ()
                && _res = FALSE (),
            _measure2,
            IF ( _flagresult = TRUE (), _flagresult, BLANK () )
        )
    )

vpollymsft_0-1652850829872.png

 

Best Regards

Community Support Team _ Polly

 

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

 

View solution in original post

6 REPLIES 6
Syndicate_Admin
Administrator
Administrator

I have the same problem, I tried the code and it works for some dates, others bring me "True". Help please.

Anonymous
Not applicable

Hi @Labraham36 ,

I have modify the measure. Please refer to it to see if it helps you.

Create a flag column on calendr table.

flag =
VAR _workday =
    WEEKDAY ( 'Table 2'[Date], 2 )
RETURN
    IF ( _workday > 5, FALSE (), TRUE () )

Then create a measure.

Measure_4 =
VAR _plus =
    MAX ( 'Table'[date] ) + MAX ( 'Table'[value] )
VAR _days =
    CALCULATE (
        COUNT ( 'Table 2'[Date] ),
        FILTER (
            ALL ( 'Table 2' ),
            'Table 2'[Date] >= MAX ( 'Table'[date] )
                && 'Table 2'[Date] <= _plus
                && 'Table 2'[flag] = FALSE ()
        )
    )
VAR _retyrnda = _plus + _days
VAR _flagresult =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _retyrnda )
    )
VAR _measure1 = _retyrnda + 1
VAR _res =
    CALCULATE (
        SELECTEDVALUE ( 'Table 2'[flag] ),
        FILTER ( ALL ( 'Table 2' ), 'Table 2'[Date] = _measure1 )
    )
VAR _measure2 = _retyrnda + 2
RETURN
    IF (
        _flagresult = FALSE ()
            && _res = TRUE (),
        _measure1,
        IF (
            _flagresult = FALSE ()
                && _res = FALSE (),
            _measure2,
            IF ( _flagresult = TRUE (), _flagresult, BLANK () )
        )
    )

vpollymsft_0-1652850829872.png

 

Best Regards

Community Support Team _ Polly

 

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

 

Anonymous
Not applicable

Hi @Labraham36 ,

Do you have two tables?
One is a data table and the other is a date table marked with a non-working day of false.

If so, please refer to my pbix file to see if it help you.

Create relationships between the tables.

vpollymsft_0-1652770160703.png

Then create a measure.

Measure =
VAR _a =
    MAX ( 'Table'[date] ) + MAX ( 'Table'[value] )
RETURN
    CALCULATE ( _a, FILTER ( ALL ( 'Table 2' ), 'Table 2'[Column] = TRUE () ) )

vpollymsft_1-1652770226265.png

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

 

Best Regards

Community Support Team _ Polly

 

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

 

 

Thank you very much for the proposed solution!

 

I think we are close, but it is not giving correctly. For eg; For January 9, if we add 15 working days, the date that should be returned is January 28, not 24, since the result it is giving is the same as adding the days to the date directly. Surely there must be a small error of logic.

tom480
Resolver I
Resolver I

Hi @Labraham36 ,

 

Without seeing the tables or some data, I think this article will explain it better than I could in a post.  I think this describes exactly what you are looking for.  If the link is beneficial, please mark as a solution to help others find the solution too!  Respectfully, Tom

 

Counting working days in DAX - SQLBI 

Thank you very much for the reply!

 

It's something similar, but not specifically that. (In fact I have that same development on the board, but I use it for something else). I'll see if I explain myself better:

 

I have two columns. One with the date the sale occurs, for example, and another column with the number of business days before the expiration of said sale. Ex:

 

Sale date: 3/1/2022

Expiration business days: 10

 

Now, what I need with this data is to obtain the expiration date. Logically, if I add the date and the number, the result will be 1/13/2022, but I would not be considering that the days are only business days, and therein lies my problem. The result should actually be 1/17/2022.

 

I hope I have been more clear. Thank you!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.