Date calculation considering last day of month

[Anonymous]

I have a fact table and two dimension tables in the Power BI dataset:

The Employee Dimension (In this case is the same person with different IDs)

Sk_Employee	Employee_Id	Employee_Name	Active	Department	Condition	Start_Date	End_Date
12689	100863	Jonh Doe	0	Manufacture	Temporary	2020-11-01	2021-02-01
12690	100863	Jonh Doe	0	Sales	Temporary	2021-02-01	2099-01-01
12691	5682	Jonh Doe	1	Engineering	Effective	2021-02-08	2021-05-10
12692	5682	Jonh Doe	1	Sales	Effective	2021-05-10	2099-01-01

The Date Dimension

Sk_Date	Date	Year	Year_Month
20200101	2020-01-01	2020	2020M01
20200102	2020-01-02	2020	2020M01
...	...	...	...
20211230	2021-12-30	2021	2021M12
20211231	2021-12-31	2021	2021M12

The Fact Table

Sk_Data	Sk_Employee
20201101	12689
20201102	12689
20201103	12689
...	...
20210201	12690
20210202	12690
20210203	12690
...	...
20210208	12691
20210209	12691
20210210	12691
...	...
20210510	12692
20210511	12692
20210512	12692
...	...

The join to populate the fact table consider the date range between Start_Date and End_Date, something like:

SELECT
    Sk_Date
    ,Sk_Employee
FROM
    DateDimension AS DD
    LEFT JOIN EmployeeDimension AS ED
        ON DD.Date >= ED.Start_Date
        AND DD.Date <= ED.End_Date

In the Power BI there is a calaculated measure to count distinct employee id in fact table:

EmployeeQty = CALCULATE(
    DISTINCTCOUNT(
        'EmployeeDimension'[Employee_Id]
    )
)

In a Power BI Matrix visualization I have the result below (considering in matrix rows [Employee_Name], [Employee_Id] and [Condition]. For Columns date from dimension date [Year_Month] and for values the calculated measure [EmployeeQty]):

Employee_Name	2020M11	2020M12	2021M01	2021M02	2021M03	2021M04	2021M05
+ Jonh Doe	1	1	1	2	1	1	1
+ 5682				1	1	1	1
Effective				1	1	1	1
+ 100863	1	1	1	1
Temporary	1	1	1	1
Total	1	1	1	2	1	1	1

So, now I need to create a measure to avoid overlap in month with changes. The rule is: If the change occurred in the last day of month you can consider the count for the month, else month - 1. In other words, if in the example of Jonh the Condition transition occurred in the last day of month (in this example 2021-02-28) I can consider the value 1 for February, else only until January. The problem is to change to Temporary to Effective Jonh was fired as Temporary and Hired as Effective with new ID and the End_Date in EmployeeDimension have the last record for each ID with 2099-01-01 data.

The desired result in Power BI Matrix is (difference is in the column 2021M02):

Employee_Name	2020M11	2020M12	2021M01	2021M02	2021M03	2021M04	2021M05
+ Jonh Doe	1	1	1	1	1	1	1
+ 5682				1	1	1	1
Effective				1	1	1	1
+ 100863	1	1	1
Temporary	1	1	1
Total	1	1	1	1	1	1	1

I tried to create the measure:

NewEmployeeQty = CALCULATE(
    DISTINCTCOUNT(
        'EmployeeDimension'[Employee_Id]
    ),FILTER(
        'EmployeeDimension','EmployeeDimension'[End_Date] < EOMONTH('EmployeeDimension'[End_Date].[Date],0)
    )
)

But the result is the same of measure EmployeeQty. Does anybody know how to create this measure? Due the End_Date have 2099-01-01 value, I'm trying to use the Start_Date column too.

[TheoC]

Hi @Anonymous 

Can you try something like this:

EmployeeQty = 

VAR _DistinctQtyEmp = DISTINCTOUNT ( 'EmployeeDimension'[Employee_Id] )

RETURN 

CALCULATE ( _DistinctQtyEmp ,
	FILTER ( 'Employee Dimension' , 
		'Employee Dimension'[End_Date] <= EARLIER ( 'Date Dimension'[Date] ) && 
		'Employee Dimension'[End_Date] >= ( 'Date Dimension'[Date] ) ) 
		)

Apologies, I haven't tested the syntax.   I will take a better look shortly.

Thanks heaps,

Theo

[Anonymous]

Hi @TheoC, I tried to use but the EARLIER function don't accept value from Date Dimension (Parameter is not the correct type). I created a new variable but without success: 

EmployeeQty = 
VAR _DistinctQtyEmp = DISTINCTOUNT ( 'EmployeeDimension'[Employee_Id] )
VAR _Date = VALUES(''Date Dimension'[Date].[Date])
RETURN 
    CALCULATE ( _DistinctQtyEmp ,FILTER ( 'Employee Dimension' , 
    'Employee Dimension'[End_Date] <= EARLIER ( _Date ) && 
     'Employee Dimension'[End_Date] >= ( _Date ) 
    )
)

[TheoC]

Hi @Anonymous , did you use it as a measure or calculated column? EARLIER only works with Calculated Columns. If it has to be measure,  we'll need to modify.

[Anonymous]

Sorry @TheoC, I got your point. I'm using the EmployeeQty as a Measure.

[TheoC]

@Anonymous did it end up working?

[Anonymous]

@TheoC not working yet, I created a column with last day of month from End_Date. I'm trying to create a measure similar your example case when End_Date < Last_Month_Date then DATEADD End_Date, -1, MONTH. 

Sk_Employee	Employee_Id	Employee_Name	End_Date	Last_Month_Date
12689	100863	Jonh Doe	2021-02-01	2021-02-28
12690	100863	Jonh Doe	2099-01-01	2099-01-31
12691	5682	Jonh Doe	2021-05-10	2021-05-31
12692	5682	Jonh Doe	2099-01-01	2099-01-31

[Anonymous · Accepted solution]

Hi @TheoC, I couldn't apply the workaround using DAX (by my limitation at this moment), but I could get the result appliyng case when in fact load where clausule. Anyway thank you very much for your time in share knowlodge!

SELECT
    Sk_Date
    ,Sk_Employee
FROM
    DateDimension AS DD
    LEFT JOIN EmployeeDimension AS ED
        ON DD.Date >= ED.Start_Date
        AND DD.Date <= CASE WHEN ED.End_Date < EOMONTH(ED.End_Date,0) THEN DATEADD(MONTH, -1, ED.End_Date) ELSE ED.End_Date END

[TheoC]

@Anonymous that's very strange. I will take a closer look when in front of computer.  Currently on phone, apologies!

@amitchandak, is this something you could assist with? Apologies for tagging you. However, I trust your knowledge.