Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jan_Trummel
Helper III
Helper III

Number of months present in the reporting period

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 dateIn 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 modelThere are 3 tables in my model

 

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

 

The table dimDateSmallThe 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 2023The 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

2 REPLIES 2
ThxAlot
Super User
Super User

HR DATEDIFF.pbix

 

ThxAlot_0-1693679585240.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



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" columnThe "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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.