Virtual Table of Weekdays between two date columns

DAX DAX Calculations
1

I have an interesting challenge that I’m sure there’s a simple solution for, but I haven’t been able to solve it. I have a table from an Azure SQL database which tracks my team’s individual annual goals for billable hours. Different people have different goal amounts, and to further complicate it the goals might change over time. For example, a new hire might have a goal of 5 billable hours per week for their first month, 10 their second month, 15 their third month, etc. The fact table looks as follows:

image
image1121×485 22.9 KB

In this sample we’ve got 3 different employees and 8 different records. What I need to do is create a measure that allows me to aggregate the total goal hours by employee, by date. I can easily add a column to the fact table that converts the annual goal to a daily goal ::: Daily Goal Hours = DIVIDE( [Annual Goal Hours], 260, 0), and I can use the Employee ID field to join this fact table to my Employee lookup table to allow me to slice and filter by department, supervisor, etc.

I assume I need to use some version of SUMX to iterate through the Daily Goal Hours by each date and filter to only the dates in range, multiplying the Daily Goal Hours by the number of Weekdays in that range. To use the example above, the aggregate Q1 2020 goal for these three employees would be 887 Hours.

What I haven’t been able to figure out is how to relate the list of dates between From Date and To Date to the daily goal. I think I need to create a table or virtual table with columns for Employee, Date, and Daily Goal, where all of the dates between “From Date” and “To Date” are unpivoted, and weekends are filtered out, but I’m stuck on how to do that. Or maybe there’s a more efficient way to reference a date value between two date columns and lookup a value in that row?

2

DO you have a Date table in your data model?

3

Yes, I have a date table with an inactive relationship to both the To Date and From Date columns. Here’s the full model:

image
image2917×1443 282 KB

4

Hi,
You may wish to have a look at the “Events in progress” pattern, calculating between two dates, regularly referred to in this forum.
Paul

5

Thanks Paul, the “events in progress” is the phrase I wasn’t familiar with – searching that term gave me some better results than my previous attempts.

I’ve used this type of calculation many times in the past, but this particular application is challenging because the fact table doesn’t include a value for each date that can be summed. I found a thread with a similar problem, and the solution was to modify the table in Power Query instead of using DAX, but it doesn’t actually explain how to do so: Calculate contract value daily - #2 by sam.mckay

I’ve uploaded a copy of my file, with a DAX solution I came up with today that seems to work for the most part. But maybe transforming the table in Power Query would be a better/faster/more stable solution? How would I go about adding a row for each record and for each date between the From Date and the To Date? Again, I’ve searched the forum for an example but haven’t found one yet.

Here’s what I did to solve it (I think) with DAX:

  • Added a column to change the 12/31/3000 end dates to the max date in my date table (12/31/2020)
  • Added a column to convert the annual goal to a daily goal
  • Created a measure for Workdays (excludes weekends and holidays per the holiday table in the model)
  • Used the events in progress structure to create a measure for the Active Daily Goal Hours
  • Used the events in progress structure to create a measure for the Active Workdays, utilizing a variable that calculates the dates between the MAX( [Goal Start Date], [First Date in Slicer] ) and the MIN( [Goal End Date], [Last Date in Slicer])
  • Multiplied the two measures above, wrapped in SUMX( ALLSELECTED( [Goal Table] ) ) so that it responds to the slicers correctly

I think this does the job, and it seems to perform quickly enough, so maybe doing the Power Query transformation isn’t necessary unless I need to get into some more complex table visuals.

Billing Goal Demo.pbix (669.7 KB)

6

.
Based on the data you have, I think this will do the trick.
Go to your Employee Annual Goal Table SQL table, add a Custom Column and enter this M code:

List.Select( 
    List.Difference( 
        List.Dates([FromDate], Number.From([ToDate] - [FromDate]) +1, Duration.From(1)),
    List.Transform(#"Holiday Table"[Holiday Date], Date.From )), 
each Date.DayOfWeek(_, 1) < 5 )

From the inside out this does the following:

  1. create a list of dates from the [FromDate] until the [ToDate]
  2. removes all dates that are present in #“Holiday Table”[Holiday Date]
  3. removes all dates that are a saterday and sunday

With the side ward arrows you can select “Expand to New Rows”

I hope this is helpful.

3 Likes
7

Thanks Melissa, you create magic with M. Indeed, strong preference for solving in Power Query if possible.
Paul

8

Thanks so much, Melissa, this is the Power Query solution I was looking for.

I have one additional question to optimize this further. In the database this table is coming from, if the “From Date” is blank, it defaults to 1/1/1900, and if the “To Date” is blank it defaults to 1/1/3000. Obviously this creates a huge number of unnecessary rows in my expanded table. I removed these rows by simply adding a date filter on the Custom column to remove anything before 1/1/2016 and after 12/31/2020.

I’m wondering if there’s a way in Power Query to use the Max date in my Dates table as the upper limit instead of manually setting the filter to 12/31/2020?

9

Quick question @TWhite

Is the lower limit fixed on 1-1-2016? I can imagine you’ll want a more dynamic approach for the upper limit? If so, what would the required logic be? OR Would you rather like to arrange that through the Date table, I mean identifying the lower- and upper date boundaries?

Thanks!

10

Yes, the lower limit can simply be fixed at 1/1/16, and it’s only the upper limit that I want to behave dynamically. Two options that come to mind:

  • Set the upper limit to the max date in my Dates table
  • Set the upper limit to today’s date + 1 year (or some similar date adjustment)

Either one of these would be sufficient.

11

Okay so give this a try.

  1. Create a New from Blank Query and call it MinDate
    image

  2. Create another called MaxDate
    image

  3. Change the Custom Column M code to

    image
    image1491×253 19 KB 

     let 
     LBound = if [FromDate] < MinDate then MinDate else [FromDate], 
     UBound = if [ToDate] > MaxDate then MaxDate else [ToDate] 
 in 
 List.Select( 
     List.Difference( 
         List.Dates( LBound, Number.From( UBound - LBound ) +1, Duration.From(1)), 
     List.Transform(#"Holiday Table"[Holiday Date], Date.From )), 
 each Date.DayOfWeek(_, 1) < 5 )

.
I hope this is helpful.

1 Like
12

Works perfectly! Thank you!