Business Day Calculation

DAX DAX Calculations
1

Hello,

I am trying to create a projection based on an average multiplied by the number of business days in the month as well as account for holidays.

I have a rolling dynamic calendar that adds a new date automatically every day and have a calculation already to count the business days. My challenge is that it only counts bussiness days based of the rolling calendar and therefore does not give me an accurate count for the whole month.

This has lead me to create a separate static date table. The problem is I have not been able to relate the two calendars together. I have a column in my static date table that tells if its the current month but its very limited as it will not work with a date slicer.

Any thoughts on how I might do this all from my dynamic calendar? Or if that’s not possible have the static calendar count business days based of the date selection.

I know this was a little complicated so I attached a sample file. Any help would appreciated. I have been scratching[Counting Business Days my head a little on this one.

Counting Business Days.pbix (72.1 KB)

2

Hi Matthew,

In the resources of the Advanced atea Transformations & Modelling course (link below) you’ll find the M code for a Date Table function. Most of what you’ll ever need is included but should the need arise you can customize it to better fit your needs. Like adding holidays so you can exclude them as workingdays and/or adding date offsets so you can move through time relative to TODAY’s date.

The key is that there’s just one Date table in your model - that does it all.

I’m confident you’ll resolve all issues by taking the time to create a proper Date table.

3

Melissa,

Would this approach still work even though I am using a dynamic rolling calendar or would I have to abandon that approach?

4

Try thinking of it in this way, the date is a Dimension. So all attributes you might need in regard to dates should be present in that one Date Dimension table.

  1. Built a proper Dates table for full year(s) (with the information provided in my first reply)
  2. Add a custom column to the Dates table named [Dynamic rolling date] and have that equal TRUE for every date in your calendar it applies to, something like: [Date] <= Date.From( DateTime.FixedLocalNow())
    not sure how far back you want this to go OR if more logic needs to be applied here
  3. In visuals, slicers, filters and measures you are now able to refer to the Date[dynamic rolling date] so you can control the Dates that are ‘visible’ in that context

Now every time the Date table Query gets refreshed the evaluation of: [Date] <= Date.From( DateTime.FixedLocalNow()) gets applied making it fully dynamic.

5

Melissa,

Thank you very much. If I’m hearing you correctly the rolling calendar I created just isn’t practical in my use case I have. Instead I need to put full years in. I’ll update my models and give that a shot.

I assume that I would need to add new years as necessary. I’ll let you know what my result is. Thanks for your help.

6

One more tip, you can add new years fully dynamic as well

The M code below does the following:

  1. It determines what datetime it is now (so when executed)
  2. Only keeps the date part of that datetime
  3. returns the laste date of that year (31-12)
  4. and adds one year

EndDate = Date.AddYears(Date.EndOfYear(Date.From(DateTime.FixedLocalNow())),1)

So when executed NOW the value of step 4 is returned.

  1. 29-01-2020 16:27
  2. 29-01-2020
  3. 31-12-2020
  4. 31-12-2021
7

Melissa,

Than you so much. This has been very helpful. I may have some follow up questions but I will start with this.

1 Like
8

Your welcome Matthew.

Just give a shout when you’re at that stage. All the best.