Number of months present in the reporting period

[Jan_Trummel]

Hello to the forum,

I would like to discuss a measure with you. I would like to know how you evaluate my solution in terms of usefulness, performance and use of the filter context.

The task

In the dimPersons table we can find information about 10 employees. We can see when a person joined the company and when he left or will leave.

In the dimPersons table, each employee has an entry and exit date

In the report, I filter for a specific year and month. Power BI should only show people who were present in the company in the selected reporting year.

Example:

If I filter for September 2023, then only these people should appear:

• Charlie
• Alan
• Jake
• Judith
• Walden
• Kandi
• Herb

In addition, Power BI should calculate how many months these people are present in the company in the selected reporting year. The entire reporting year should be taken into account, regardless of the reporting month selected.

In addition, there is the following rule: if a person joins the company before the 15th day of the month, we count the month as a full month. On the other hand, if the person entered on the 15th day of the month or later, then we only count the month as half a month.

Example:
Jake joined the company on 08/17/2023. So we only count the month of August 2023 as half a month because it occurred after the 15th day.
Judith joined the company on October 7th, 2023. We count the month of October 2023 as a full month because it occurred before the 15th day.

The same applies to the departure date: if a person leaves on or after the 15th day of the month, we count the month as a full month. On the other hand, if the person leaves before the 15th day of the month, we only count the month as half a month.

Example:
Chelsea left the company on April 14, 2021. We therefore only count the month of April 2021 as half a month.
Herb left the company on 05/18/2023. So we count the month of May 2022 as a full month.

The data model

Besides the dimPersons table, I also have a date table called dimDate and a small date table called dimDateSmall (I'll tell you the reason for this small date table later).

There are 3 tables in my model

The table dimDateSmall has only 2 rows for each month: the 1st and the 15th day.

The table dimDateSmall

My measure

With this measure I solve the task:

Months in company =
VAR vdimDate_whole_year = CALCULATETABLE(DimDate, ALLEXCEPT(DimDate, DimDate[Year]))
VAR vMinDate = MINX(vdimDate_whole_year, DimDate[Date])
VAR vMaxDate = MAXX(vdimDate_whole_year, DimDate[Date])
VAR vResult =
SUMX(
    DimPersons,
    --Day, Month number and year of entry date
    VAR vDayEntryDate = DAY(DimPersons[Entry date])
    VAR vMonthEntryDate = MONTH(DimPersons[Entry date])
    VAR vYearEntryDate = YEAR(DimPersons[Entry date])

    --Day, Month number and year of exit date
    VAR vDayExitDate = DAY(DimPersons[Exit date])
    VAR vMonthExitDate = MONTH(DimPersons[Exit date])
    VAR vYearExitDate = YEAR(DimPersons[Exit date])

    --New entry date
    VAR vNewEntryDate =
    IF(
        vDayEntryDate >= 15,
        DATE(vYearEntryDate, vMonthEntryDate, 15),
        DATE(vYearEntryDate, vMonthEntryDate, 1)
    )

    --New exit date =
    VAR vNewExitDate =
    IF(
        vDayExitDate >= 15,
        DATE(vYearExitDate, vMonthExitDate, 15),
        DATE(vYearExitDate, vMonthExitDate, 1)
    )
    --Number of lines in dimDateSmall
    VAR vNumberOfLines_DimDateSmall =
    COUNTROWS(
        CALCULATETABLE(
            DimDateSmall,
            DimDateSmall[Date] >= vNewEntryDate,
            DimDateSmall[Date] <= vNewExitDate,
            DimDateSmall[Date] >= vMinDate,
            DimDateSmall[Date] <= vMaxDate
        )
    )
    VAR vNumberOfLines_DividedByTwo =
    vNumberOfLines_DimDateSmall / 2
    RETURN
    vNumberOfLines_DividedByTwo
)
RETURN
vResult

In the following I go through the individual statements in the measure:

VAR vdimDate_whole_year = CALCULATETABLE(DimDate, ALLEXCEPT(DimDate, DimDate[Year]))

I filter the dimDate table for the selected reporting year.

VAR vMinDate = MINX(vdimDate_whole_year, DimDate[Date])
VAR vMaxDate = MAXX(vdimDate_whole_year, DimDate[Date])

I store the first and last date of the reporting year in each of the two variables.
Then I run through the dimPersons table with SUMX and create the following variables:

VAR vDayEntryDate = DAY(DimPersons[Entry date])
VAR vMonthEntryDate = MONTH(DimPersons[Entry date])
VAR vYearEntryDate = YEAR(DimPersons[Entry date])
VAR vDayExitDate = DAY(DimPersons[Exit date])
VAR vMonthExitDate = MONTH(DimPersons[Exit date])
VAR vYearExitDate = YEAR(DimPersons[Exit date])

I break down the entry and exit dates into their day, month, and year components.

VAR vNewEntryDate =
IF(
    vDayEntryDate >= 15,
    DATE(vYearEntryDate, vMonthEntryDate, 15),
    DATE(vYearEntryDate, vMonthEntryDate, 1)
)

I create a new entry date. I am checking if the entry date day is greater than or equal to 15. If so, I create the new date with the 15th day, otherwise with the 1st day.

VAR vNewExitDate =
IF(
    vDayExitDate >= 15,
    DATE(vYearExitDate, vMonthExitDate, 15),
    DATE(vYearExitDate, vMonthExitDate, 1)
)

I create a new exit date. Again, I check whether the leaving date is greater than or equal to 15. If so, I set the new date to the 15th day here as well, otherwise to the 1st day.

VAR vNumberOfLines_DimDateSmall =
COUNTROWS(
    CALCULATETABLE(
        DimDateSmall,
        DimDateSmall[Date] >= vNewEntryDate,
        DimDateSmall[Date] <= vNewExitDate,
        DimDateSmall[Date] >= vMinDate,
        DimDateSmall[Date] <= vMaxDate
    )
)

In the small date table dimDateSmall I count all rows that are in the selected reporting year and in the employee's attendance period at the same time. So I get 2 rows for every full month and one row for every half month.

VAR vNumberOfLines_DividedByTwo =
vNumberOfLines_DimDateSmall / 2

Finally, I divide the number of rows by 2 to get the correct number of months (including any half months).

The result

Here is the result for the reporting month September 2023:

The result for September 2023

Now I'm curious about your opinion! Do you have suggestions for improvement or maybe completely different ideas to solve the task? I look forward to a stimulating discussion with you!

Best regards
Jan

[ThxAlot]

HR DATEDIFF.pbix

[Jan_Trummel]

Hello @ThxAlot ,

many thanks for your response! I think your approach is extremely good (even if it doesn't quite solve my problem - maybe I didn't describe the requirement well enough).

The "Months in company" column contains the required results that do not match the values in the "Duration" column

In any case, I can already see that you can get by with far fewer lines of code than I did in my solution.

I will try to incorporate your approach into my measure and then publish it here again.

Thank you once again for your work!

Best regards

Jan