How to Calculate a new date using a Calendar table

[MartinBakker]

Hi,

I have been creating simple Excel-like reports using Power BI so far. I am more experienced in SQL so up until now I managed to solve most Power BI issues in the SQL queries that retrieved the data for Power BI.

I have an issue now in calculating a date using a Calendar table.

I want to calculate a Receipt Date using an Order Date and a Supply Time (in work days).

I searched multiple forums over the past days but generally can only find topics on calculating the number of (work)days between two dates in a calendar.

As said I have a table with dates which excludes weekends and holidays already which looks like this:

A section of the order table with Order Dates and Supply Times looks like this:

My idea was to use the Calendar count column to calculate the 'Calculated Receipt Date'.

Example:

Order Date = 2021-07-15

Supply Time = 7

Calculated Receipt Date = 2021-07-15 + 7 days which according to the Calendar dates would be 2021-07-26

Since I am a novice at DAX etcetera, I don't even know where to start when I want to add a new column to the order table.

1. Should I do this in the Power Query Editor or should I add a new column (or even a measure) in the Modeling section of the Power BI Desktop?
2. Can this Calculated actually be calculated and if so, what would the correct calculation be?

Any help would be appreciated.

Regards,

Martin Bakker

[v-stephen-msft]

Hi @MartinBakker ,

You can create a calculated column like

Calculated Receipt Date = [Order Date]+DAY([Supply Time])

Best Regards,

Stephen Tao

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

[MartinBakker]

Thanks for your suggestion Stephen but that does not exclude weekends and holidays.

My Calendar table only contains the actual work days in my company.

So I am looking for a way to calculate the date using this calendar.

Let's say that the order date is Friday August 20.

The supply time is 10 days and Monday August 30 is a holiday.

The calculated date without the two weekends and the holiday would be September 6.

I can imagine that I am not the first with this requirement. I was hoping for a reasonably easy solution 😉

Any thought?

[v-stephen-msft]

Hi @MartinBakker ,

I think you should have a table specifically recording which day of the holiday is, like the following

Create the following calendar table

Calendar =
FILTER (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 8, 1 ), DATE ( 2021, 9, 31 ) ),
        "Is working days",
            IF (
                WEEKDAY ( [Date], 2 ) = 6
                    || WEEKDAY ( [Date], 2 ) = 7
                    || MAX ( 'Holiday'[Date] ) = [Date],
                "N",
                "Y"
            )
    ),
    [Is working days] = "Y"
)

Then create a rank column in the calendar

Rank = RANKX('Calendar',[Date],,ASC,Dense)

Create the measure in the main table

Calculated Receipt Date =
VAR _rank =
    CALCULATE (
        MAX ( 'Calendar'[Rank] ),
        FILTER ( 'Calendar', [Date] = MAX ( 'Table'[Order Date] ) )
    )
        + MAX ( 'Table'[Supply Time] )
RETURN
    CALCULATE ( MAX ( 'Calendar'[Date] ), FILTER ( 'Calendar', [Rank] = _rank ) )

Check the details in the attachment.

Best Regards,

Stephen Tao

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

[MartinBakker · Accepted solution]

Found it!

I replaced the MAX ( 'Holiday'[Date] ) = [Date] part with a filter that compared calendar dates with the holiday dates.

CALCULATE( COUNTROWS(Holiday), FILTER( Holiday, Holiday[Date] = EARLIER([Date]) ) )

Now the Calendar excludes the holidays.

Thanks for helping me on my way.

[MartinBakker]

Thanks again Stephen.

I appreciate your help very much.

Your example and attachment are very clear.

Unfortunately when I apply it to my report, the holydays are somehow not filtered out of the calender.

I have for instance 2021-12-27 in my Holiday table.

I altered the definition of the table itself by using a MIN and MAX for the dates and added 365 to the MAX date.

But other than that I copied the Calender table definition from your example.

Still 2021-12-27 is in my calendar.

Just to be sure, I added 2021-12-27 to the example in your attachment and of course it works there. 😉

I think I am almost there but what am I missing?

[amitchandak]

@MartinBakker , refer my blog , how to add workdays

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[MartinBakker]

Hi Amit,

Thank you very much for your suggestion.

My calendar does not include weekend days nor holidays so I assume that part (the Rank part?) in your blog is not applicable to my question(?)

Can I try to re-use the part of your blog which says 'Now use it:' to calculate the Calculated Date?

I am afraid that, being a novice to Power BI, the rank part is blurring my vision.