Power Query: Create AsOf Date as beginning of next month by adding to a date column

[dgkallan]

I'm trying this as a possible solution to my other problem:  https://community.fabric.microsoft.com/t5/Power-Query/HR-Turnover-Rate/m-p/3497390

In Power Query, I want to create as AsOf date for when an employee first starts counting toward the beginning of month employee counts.  Basically the logic is:  Create a custom column that takes the Hire Date and adds the number of days necessary to make the date the beginning of the next month.  So, if someone is hired Jan 15, add 17 days to make their AsOf Date Feb 1.  My goal in doing this is to be able to use that AsOf date and compare it to a variable called startdate and say >= (with a few other filters)

[dgkallan · Accepted solution]

I made these adjustments and worked!!!! Thank you so much!  I may have give some incorrect logic.

Avg Employees =

VAR startdate = MIN(DateTable[Date])

VAR enddate = MAX(DateTable[Date])

VAR EmpsatStart =

CALCULATE (

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

         TestEmployees,

         TestEmployees[Hire Date] < startdate

         && (TestEmployees[Term Date] >= startdate || ISBLANK(TestEmployees[Term Date]))

         )

    )

VAR Hires =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date] <= enddate && TestEmployees[Hire Date] >= startdate)

    )

VAR Terminations =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Term Date] <= enddate && TestEmployees[Term Date] >= startdate)

)

    RETURN DIVIDE(EmpsatStart + (EmpsatStart + Hires - Terminations), 2, 0)

[dgkallan]

Again, I really appreciate all the help.  Can I ask you for another piece of advice?  I'm struggling with how to set the relationship with the date table with two (and possibly more) dates in my employees table.  Right now, I've left the relationship empty and BI seems to be able to figure it out.  Should I define a relationship between all the dates?  When I do that it blows up the report you've been helping me with, but I don't know if I should have it.  If I just wanted to run a normal report that said, show me all employees with "Active" it gets really funky though I probably should just use the empsatEnd logic for that same thing.

[BA_Pete]

Ok, so the measures I gave you won't work with an active relationship between the calendar and the fact tables, but that doesn't mean you can't have them.
The way I personally tend to set up multi-date relationships is to relate calendar[date] to all the fact table dates I want, then select the first realtionship you made (as this will be the only active one) and change it to Inactive.

When you want to actually use one of these relationships to calculate something, you add the USERELATIONSHIP function to activate it for the purposes of the calculation.

For example, you could have two Inactive relationships between Calendar[date] and (1) fact[Hire Date] and (2) fact[Term Date]. You would activate these like this:

_noofEmpsHired_byDate =
CALCULATE(
    DISTINCTCOUNT(fact[EEID]),
    USERELATIONSHIP(calendar[date], fact[Hire Date])
)

_noofEmpsLeft_byDate =
CALCULATE(
    DISTINCTCOUNT(fact[EEID]),
    USERELATIONSHIP(calendar[date], fact[Term Date])
)

Pete

[dgkallan]

**EDIT**

Those formulas all worked.   I did have an issue where the counts were repeating, but that was an issue where I had the relationship as 1:Many.  

The problems is that now my Employees at End is screwed up again.

EmpsatEnd =

var _dtEnd = MAX(dimDate[Date])

var empsatEOP =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date]<=_dtEnd

        && TestEmployees[Term Date]>_dtEnd || ISBLANK(TestEmployees[Term Date]))

)

RETURN empsatEOP

I verified the relationship are all inactive.

I haven't solved my question here, but I did figure out that it is counting the 5 employees without term dates when it does it's count.  To make it simple, let's talk about a month - The logic of that measure is count employees where the hire date is before the end of the month, they were not terminated before the end of the month nor terminated at all.  So, it's looking past the beginning of the month to find those folks.  Any ideas?

[BA_Pete · Accepted solution]

It looks like your bracketing is slightly incorrect around the last FILTER condition:

Try this, where I've surrounded the two OR conditions in brackets:

EmpsatEnd =
var _dtEnd = MAX(dimDate[Date])
var empsatEOP =
CALCULATE(
    DISTINCTCOUNT(TestEmployees[EEID]),
    FILTER (
        TestEmployees,
        TestEmployees[Hire Date] <= _dtEnd
        && ( TestEmployees[Term Date] > _dtEnd || ISBLANK(TestEmployees[Term Date]) )
    )
)
RETURN empsatEOP

Pete

[dgkallan]

That solved it!

[dgkallan]

@BA_Pete,

I'm hoping I might solicit your help again.  I finally got the full data set and for some reason the old logic doesn't quite work right.

Using the data in Excel, there a validated set of unique employeeIDs (EEID) of 2,868.  When I filter for the start of the period (using November to keep it simple), I take the HIre Dt <= 11/1/2023 and the Terminations after 11/1/2023 or blank and I end up with 1,390.  This should be the result I'm getting in PowerBI.  

For the end of the period, I filter by anyone hired prior to 11/30/2023 and the termination date is blank or after 11/30/2023, and I end up with 1,402.  The average of these two should give me 1,396 as the average.

In PowerBI I get a beginning of period count of 1,385 and an end of period count of 1,397 and I can't figure out why.

The relevant columns are pretty simple:  EEID, Original Hire Dt, and Termination Dt.

In my Date table, I have all dates, I built all of my relationships and they are all Inactive - consistent with what you advised before.

This is my code for the beginning of the period:

BOP Employee Ct =

VAR _bop = MIN(dimDate[Date])

VAR _eop = MAX(dimDate[Date])

VAR _empsatbop =

CALCULATE(

    DISTINCTCOUNT(Employee[EEID]),

    FILTER(

        Employee,

        Employee[Original Hire Date]<=_bop

        && (ISBLANK(Employee[Termination Date]) || Employee[Termination Date]>=_bop)

        )

)

RETURN _empsatbop

This is my code for the emloyees at the end of the period:

EOP Employee Ct =

VAR _bop = MIN(dimDate[Date])

VAR _eop = MAX(dimDate[Date])

VAR _empsateop =

CALCULATE(

    DISTINCTCOUNT(Employee[EEID]),

    FILTER(

        Employee,

        Employee[Original Hire Date]<=_eop

        && (Employee[Termination Date]>=_eop || ISBLANK(Employee[Termination Date]))

    )

)

RETURN _empsateop

The visual has a filter of November 2023 from the Date table

Relationships:

I'm hoping you can help

[BA_Pete]

Are you able to share a full anonymised example of your new data please? I.e. ALL rows for [EEID], [Original Hire Date], and [Termination Date] relevant for November. I want to make sure I'm using exactly the same data as you (for November at least).

As this is a large amount of data, I would recommened sharing as follows:

-1- In Power Query, select these three columns in your Employee table and select Remove Other Columns

-2- Filter [Original Hire Date] < 1st Nov 23 and [Termination Date] > 31st Oct 23 or null/blank.

-3- Click the small table icon at the top left of the PQ table and select 'Copy Entire Table'.

-4- If your filtered table was <= 10,000 rows, then go to the Home tab > Enter Data and paste your table in there, then copy all the code from the Advanced Editor for that query and paste into a code window ( </> button) here.

-5- If your filtered table was > 10,000 rows then you'll need to paste into Excel and share via OneDrive/Google Drive and paste the link here.

Pete

[dgkallan]

I went through the extract of Power BI and the issue comes down to this.  There are five EEIDs in my Excel report that start with C#### that PowerBI isn't picking up and that is the difference.  So, I went into PowerBI and the changed the EEIDs to a Text field, which I assumed would fix it.  It ia still staying at 1385.  

I put the data in Google Sheets

[BA_Pete]

Your Sheets link requires sign-in. Can you update it to public please?

Pete

[dgkallan]

Changed it to anyone with a link 

[BA_Pete]

Hi @dgkallan ,

I've read through the other thread and what you've asked for here is not the solution you want.

You just need to use the correct measure to calculate average employees dynamically and everything else should work as required.

I think it would be something like this:

_avgEmployees = 
VAR __dtStart = MIN(dimDate[Date])
VAR __empsAtStart = 
CALCULATE(
    DISTINCTCOUNT(YourTable[EmployeeID]),
    FILTER(
        YourTable, 
        YourTable[HireDate] <= __dtStart 
        && ( YourTable[TerminationDate] >= __dtStart || ISBLANK(YourTable[TerminationDate]) )
    )
)
VAR __dtEnd = MAX(dimDate[Date])
VAR __empsAtEnd = 
CALCULATE(
    DISTINCTCOUNT(YourTable[EmployeeID]),
    FILTER(
        YourTable, 
        YourTable[HireDate] <= __dtEnd
        && ( YourTable[TerminationDate] >= __dtEnd || ISBLANK(YourTable[TerminationDate]) )
    )
)
RETURN
DIVIDE(__empsAtStart + __empsAtEnd, 2, 0)

Pete

[dgkallan]

Hi BA_Pete,
I appreciate the help.  The Beginning of the Period is now working out great.  I had to make the Hire Date less than (not less than or equal to) because of how I'd want to hand a new hire that month.  I'm going to look closer at the End of Period logic to make sure I got that right.  I may just want to take the beginning of period and add in hires and terms

The sample data is comprised of these 9 employees:

EEID	Name	Starting Salary	Current Salary	Term Date	Hire Date	Status	Report Date
7516	Bob	80000	85000	4/15/2023	1/7/2023	Inactive	11/2/2023
7517	Nancy	82000	90000	7/8/2023	2/4/2023	Inactive	11/2/2023
7518	Marie	85000	95000		10/15/2023	Active	11/2/2023
7519	JoAnn	87000	99000	10/6/2023	10/1/2023	Inactive	11/2/2023
7520	Mark	88000	95000		4/1/2023	Active	11/2/2023
7521	Dan	65000	72000		5/1/2023	Active	11/2/2023
7522	Dave	50000	55000		11/1/2023	Active	11/2/2023
7523	John	58000	62000	11/1/2023	9/15/2023	Inactive	11/2/2023
7524	Tom	95000	110000		11/4/2023	Active	11/2/2023

If you look through these, there is no Dec-22 nor Dec-23 data and the counts are off.

It should be

Month	Terms	EmpsatStart	EmpsatEnd
Jan	0	0	1
Feb	0	1	2
Mar	0	2	2
Apr	1	2	2
May	0	2	3
Jun	0	3	3
Jul	1	3	2
Aug	0	2	2
Sep	0	2	3
Oct	1	3	4
Nov	1	4	5

[dgkallan · Accepted solution]

I made these adjustments and worked!!!! Thank you so much!  I may have give some incorrect logic.

Avg Employees =

VAR startdate = MIN(DateTable[Date])

VAR enddate = MAX(DateTable[Date])

VAR EmpsatStart =

CALCULATE (

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

         TestEmployees,

         TestEmployees[Hire Date] < startdate

         && (TestEmployees[Term Date] >= startdate || ISBLANK(TestEmployees[Term Date]))

         )

    )

VAR Hires =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date] <= enddate && TestEmployees[Hire Date] >= startdate)

    )

VAR Terminations =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Term Date] <= enddate && TestEmployees[Term Date] >= startdate)

)

    RETURN DIVIDE(EmpsatStart + (EmpsatStart + Hires - Terminations), 2, 0)

[BA_Pete]

Hi @dgkallan ,

Far be it for me to tell you what result you actually want for your scenario, but what you've done here looks incorrect.

I'm not sure why you've bastardised the _avgEmployees measure I gave you but, to my mind, your output should look like this:

_avgEmployees = // Measure as originally given

_leavers = 
VAR __minDate = MIN(dimDate[date])
VAR __maxDate = MAX(dimDate[date])
RETURN
CALCULATE(
    DISTINCTCOUNT(yourTable[EEID]),
    FILTER(
        yourTable,
        yourTable[Term Date] >= __minDate
        && yourTable[Term Date] <= __maxDate
    )
) + 0

Ignore the "+0" on the end, that's just to make the chart columns line up!

_turnover_% = DIVIDE([_leavers], [_avgEmployees], 0)

Using these measures gives the following (what I believe to be 'correct') output:

Pete

[dgkallan]

Again, thank you BA_Pete,

For some reason when I use your code using the same data I get a different result.  I would love to use yours, but I can't.  The terminations code I had written matched yours so good there.  Really appreciate the help.  I meant no disrespect in changing the code, I just wasn't getting the same result so I bastardized because I wasn't getting the answer I was expecting

AVG Employees BA_Pete =

VAR dtStart = MIN(DateTable[Date])

var empsatbeg =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date] <= dtStart

        && TestEmployees[Term Date] >= dtStart || ISBLANK(TestEmployees[Term Date]) )

    )

VAR dtEnd = MAX(DateTable[Date])

VAR empsatrear =

CALCULATE(

    DISTINCTCOUNT(TestEmployees[EEID]),

    FILTER (

        TestEmployees,

        TestEmployees[Hire Date] <= dtEnd

        && (TestEmployees[Term Date] >= dtEnd || ISBLANK(TestEmployees[Term Date]))

        )

    )

    RETURN

    DIVIDE(empsatbeg + empsatrear,2,0)

[BA_Pete]

Hey, no disrespect/offence taken it's all good. I think bastardisation sounds like a really strong and angry word, but that's not how it was intended 🙂

I think the differences may be coming from the fact that my example uses a calendar table for the [monthYear] field e.g. [monthYear] Jan 23 = all dates in January 2023, therefore the average employees gets calculated as: ( Employees @ 1st Jan + Employees @ 31st Jan ) / 2. It looks like you're just using the first day of the month as your dimension, so you're just getting the employee count at a single point (day) in time, rather than a true average over the whole month.

Pete

[dgkallan]

Thank you for clarifying.  I really appreciate the help, so I would have hated to offend in any way.  I I build a date table and set it as such.  I'm using the month year from a "bin"/group in my report - I can't get those to always sort properly, but that's a different issue.    That said, I don't understand - though it sounds like you do - why our data would come back so differently.  

[BA_Pete]

I think there's possibly a couple of things going on here:

-1- Your calendar table isn't set up correctly and/or you're not using a Calendar[<Time Period>] column for your chart axis, and/or;

-2- You have your calendar table related to your fact table with an active relationship that's interfering with the measure calculation.

If you can review the above and confirm if any are the case, we can fix.

*EDIT* For clarity, I've broken out the __empsAtStart and __empsAtEnd variables into their own measures and tabulated on the original example data. I believe these outputs are correct so we need to identify why you're not getting these numbers (see above)

Pete

[dgkallan]

I can't tell you what changed, but I am getting the same results now.  I did not define any relationships between my date table and my Employee table.

This is my code for the date table with two parameters start date (1/1/2023) and end date (12/31/2023)

let
Source = List.Dates(StartDate,Duration.Days(EndDate-StartDate)+1,#duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
#"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
#"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
#"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Days in Month", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
#"Inserted Day" = Table.AddColumn(#"Inserted Week of Year", "Day", each Date.Day([Date]), Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
#"Added Custom" = Table.AddColumn(#"Inserted Day Name", "Year Month", each Date.ToText([Date],"yyyy-MM")),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Month Year", each Date.ToText([Date],"MMM yyyy")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Year Mo Short", each Date.ToText([Date],"yyyy MMM")),
#"Inserted Start of Year" = Table.AddColumn(#"Added Custom2", "Start of Year", each Date.StartOfYear([Date]), type date),
#"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
#"Inserted Month Name" = Table.AddColumn(#"Inserted End of Year", "Month Name", each Date.MonthName([Date]), type text)
in
#"Inserted Month Name"

The date showing in my table is create as a data group bin from date where:

Overall, the bin is the only way I've been able to get the months to show in the right order when they have a name otherwise it has to be YYYY-MM (e.g., 2023-01)

[BA_Pete]

Cool, glad it's working correctly for you. Regarding the months, add the following custom columns into your calendar in Power Query:

yearMonthKey =
Date.Year([Date]) * 100 + Date.Month([Date])

monthYearText =
Text.Combine(
    {
        Text.Start(Date.MonthName([Date]), 3),
        Text.End(Text.From(Date.Year([Date])), 2)
    },
    " "
)

Then, once you've applied these to your model, you can sort the [monthYearText] column based on the [yearMonthKey] column by selecting the [monthYearText] column in your Data list, going to Column Tools, Sort by Column, and selecting [yearMonthKey]:

Pete