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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
v-rongtiep-msft
Community Support
Community Support

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.

v-rongtiep-msft
Community Support
Community Support

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.

 

v-rongtiep-msft
Community Support
Community Support

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
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.