How to build the calendar table when we have start dates and end dates in our fact table ?

[tauhid9231]

Hello Power BI Community,

I'm currently working on a practice project involving an EmployeeData Table, and I could really use some guidance from those experienced with Power BI. Here's a breakdown of what I'm trying to accomplish:

Data Structure:

• EmployeeData Table with columns: employeeID, department, StartDate, EndDate (with nulls), and performance rating (ranging from 1 to 5).

Challenges:

1. Calendar Table Creation: I'm unsure how to approach building a calendar table using Power Query in scenarios where we have a StartDate and EndDate with nulls that we can't simply remove.
2. Handling Null Values: Since some employees still work with the company, the EndDate column contains null values. I'm unsure how to handle these nulls without simply removing them, as they represent ongoing employment. Using today's date as a placeholder doesn't seem appropriate for this scenario since I am doing the analysis on some past data.

Additional Transformations: I'm also interested in learning how to build a snapshot table using Power Query to capture all dates between the start date and the end date to further simplify later challenges in our analysis.

Any guidance or tips on how to tackle these challenges would be greatly appreciated. Thank you in advance for your assistance!

[tauhid9231]

Hey @BA_Pete ,
I have sent you the CSV file as per your suggestion, please check the DM. I was trying to solve the problem myself, and upon several trials and errors, I got very close to the desired goal. Sharing the snapshot below to show you the formula that I am using:

EmployeeID 91 started on in 8 June 2014 and ended on 18 June 2021, that said, the tenure calculation should take the end Date as "18 June 2022" and return 7.0 (as tenureToDate), and not push it to the end of the year. I've tried to make it dynamic, using the IF logic, but it's not working. Can we somehow achieve this behaviour?

It would help me learn a lot, if you can simulate that scenario with the dataset I provided. Thanks again for your patience and time.

Learning a Lot from You!

Regards,
Tauhid

[BA_Pete]

Yes, I've just started looking at the CSV but need to pause for the day job for a bit! 🙂

It looks like you're pretty close on your own though - just try adding some brackets around this bit and see if that works for you:

The commas in a CALCULATE between arguments 2 and N basically translate to "AND", so I'm wondering at-a-glance whether the OR in that line is causing confusion for the Storage/Formula engine.

I'll ping you my PBIX later on once I get some time to continue looking at it.

Pete

[tauhid9231]

Hey @BA_Pete ,
Just wanted to let you know that I have achieved the desired behavior by just using a "DateLeft" measure as the second argument of the DATEDIFF function, writing the formula below:

DateLeft =

MAXX(

    VALUES( EmployeeData[EmployeeID] ),

    CALCULATE(

        IF(
             ISBLANK(  MAX(EmployeeData[EndDate])  ),
             MAX('Calendar'[Date]),
             MAX(EmployeeData[EndDate])
        )

    )

)

I've validated the results, and it's working fine. Could you please take a look and confirm that whether this is a right approach or not?

Thanks for guiding me through this problem Pete, I've learnt a lot from you. I heartfully appreciate your patience and expertise.

Tauhid

[BA_Pete]

Hey Tauhid. Sorry I didn't get back to you yesterday, had to do the pesky day job!

Regarding your solution, if it works, it works. It's not necessarily the way I would do it (MAXX is an iterator function, so it may not be as performant as other options), but it's a working solution and shouldn't cause any noticeable performance issues over a reasonably-sized employee table.

I'll probably still work on that PBIX and drop it here at some point anyway tbh, but good job on getting to a working solution 🙂

Pete

[tauhid9231]

Completely fine Pete, I'll give it a try with your suggestions as well.

Good Day. 🫡

[tauhid9231]

Hi @BA_Pete ,

I must say, I'm pleasantly surprised by your insights regarding the snapshot table method. As a beginner, I tend to overthink things, so I genuinely appreciate you providing clarity on this matter. It's a valuable lesson learned!

Your explanation regarding using DAX measures to achieve the desired results makes perfect sense. I've actually experimented with a similar approach, utilizing DAX formulas like the one you provided for calculating Active Employees, but with a one-to-many relationship. I just used ALL(Calendar) as well in the active employees as one of the filter within the dax, and it worked seamlessly.


 

However, I did encounter some issues with certain measures when attempting to remove the relationship between the calendar and fact table. Measures like Number of Employees hired and and Number of Employees Left break without that relationship in place. So, I'm curious if there's a way to maintain the relationship while still achieving the efficiency and flexibility you mentioned earlier. Perhaps by using functions like ALL or CROSSFILTER intelligently?

The BIG ISSUE I am facing is, don't know how to filter the employeeIDs such that I get only the IDs for ALL active employees till date based on the slicer selection. For example, for the year 2021, I want to display the tenure for all 85 employees who have worked at any point in time till the end of 2021! And for the ones with no end date, I want to calculate their tenure as on the last date of the selected period. Got that?

I've even tried creating a separate table using VALUES( EmployeeData(EmployeeID) ) without any relationship but it doesn't work. I am attaching some snapshots as well for your clarity. Can you please provide some insight on that?

Looking forward to your insights on this matter!

[BA_Pete]

Ok, so about the relationships, there's a few things you can do here:

1) Keep the relationships that you use in other measures in place, but make them INACTIVE. You can reactivate them in the measures you need them by adding USERELATIONSHIP(calendar[date], EmployeeData[~date]).

2) Keep the relationships as they are and ACTIVE, but DEACTIVATE them as part of your over-time measures, something like this:

_ActiveEmployees =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    CALCULATE(
        DISTINCTCOUNT(EmployeeData[employeeID]),
        FILTER(
            EmployeeData,
            EmployeeData[StartDate] <= __cDate
            && ( __cDate < EmployeeData[EndDate] || ISBLANK(EmployeeData[EndDate]) )
        )
    ),
    CROSSFILTER(calendar[date], EmployeeData[~date], none)
)

3) You can continue to use ALL() or REMOVEFILTERS() (they're the same thing), but note that applying these over entire tables can cause unnecessary memory use.

Re: the tenure measure, I can see that you've changed what I provided, which will change the behaviour. The measure as I wrote it should give you the exact behaviour you're asking for, i.e. the VAR __cDate = MAX(calendar[date]) variable gets the maximum calendar date in scope (the end of the week, month, year etc.) and performs the calculation from the employees' start date until that date. That's why I mentioned that you must use a calendar dimension column as your visual axis - that's where the variable will pick up the max date (the end of the period being displayed) from.

If you can provide a small sample of copyable anonymised data, I'm happy to show you how the different options would work on your actual data/structure.

Pete

[tauhid9231]

Thanks for the response @BA_Pete . You've cleared my confusions regarding the calendar table and on how to handle blanks by explaning each point in simpler terms. Sorry for using that jargon without explaining my requirements enough.

See when I simply connect the Start dates and end dates to the date field in the calendar table, and build reports in which I am trying to see the number of active employees at any point in time based on some date slicers (say the year column) then it is not giving me correct results. Although I have did my research and solved this with the help of an article on "Events in Progress DAX pattern" by sqlbi. I have understood the logic using DAX, but it requires us to build complex measures each time.

To reduce that complexity while writing measures, they have mentioned about an alternative way, which is by using the snapshot table (which can be created through Power Query or by using DAX). The idea is to get every single date beween the start and end date for each employees as different rows so the filters work correctly and we can simplify our dax calculations to some extent while also improving the performance of the report.

I am linking the article here: https://www.daxpatterns.com/events-in-progress/ 

For my particular purposes, I want to build a measure that dynamically calculates the employee tenure based on the selection on different date slicers. And I am finding it hard to work with those start and end date columns, and thus looking for ways by which I can reduce that complexity to some extent.

Hope I was able to explain my struggle, if you have some simpler approach, please share it with me. It would really help me a lot. 

[BA_Pete]

Thanks for the full explanation.

Personally, I wouldn't recommend snapshot table method. This will make your data model incredibly large incredibly fast in your scenario. The DAX Patterns example uses Orders as an example, where an order open for ten days needs ten rows. In your scenario, every employee will need 365 rows for each year of employment - a completely different level of data expansion

The measures to get what you want really aren't that complicated and I think they'll be significantly faster and more efficient than your proposed method once you take transformation and refresh times into account.

Try these measures on your original table structure and see how you get on. You want to keep your calendar table UNRELATED from the fact table, but you'll need to use calendar dimensions as the axis for any visuals you want to display against time periods:

_ActiveEmployees =
VAR __cDate = MAX(calendar[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(EmployeeData[employeeID]),
    FILTER(
        EmployeeData,
        EmployeeData[StartDate] <= __cDate
        && ( __cDate < EmployeeData[EndDate] || ISBLANK(EmployeeData[EndDate]) )
    )
)

// Note that you may need to adjust the '<=' and '<' bits to get exactly the behaviour that you want

_AverageTenureToDate_Days =
VAR __cDate = MAX(calendar[date])
RETURN
AVERAGEX(
    EmployeeData,
    DATEDIFF(
        MIN(EmployeeData[StartDate]),
        __cDate,
        DAY
    )
)

Let me know how you get on with these.

Pete

[BA_Pete · Accepted solution]

Hi @tauhid9231 ,

1) I wouldn't try to build a calendar table based on fact table dates - it's inefficient and unnecessary. Just build a separate calendar table that runs from the first day of your year/fiscal year X years ago until the last day this year/ X years in the future (if you're going to be doing forecasting etc.). Personally, I use Dataflows to build and refresh such a calendar every night so it's available to import into any reports I make going forward.

2) This kind of depends on what you want to do with the data but, generally, leaving these as null is fine. In any calculation measures you write, you just specify that a null (BLANK) value is equivalent to a future date. For example, you may use a FILTER in a measure something like this:

FILTER(
    yourTable,
    yourTable[Start Date] <= __maxDate
    && (__minDate < yourTable[End Date] || ISBLANK(yourTable[End Date]))  // <---
)

The last line here basically says "where my variable date value (__minDate) is less than [End Date] OR [End Date] is BLANK.

3) Not sure exactly what you mean by "build a snapshot table". You'll need to give a bit more detail around what your intended use for this table is. My initial guess is that there's an easier/more efficient way to do what you think you need to do, but I'll let you explain what exactly that is.

Pete