cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Date calculation considering last day of month

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_EmployeeEmployee_IdEmployee_NameActiveDepartmentConditionStart_DateEnd_Date
12689100863Jonh Doe 0ManufactureTemporary2020-11-012021-02-01
12690100863Jonh Doe0SalesTemporary2021-02-012099-01-01
126915682Jonh Doe1EngineeringEffective2021-02-082021-05-10
126925682Jonh Doe1SalesEffective2021-05-102099-01-01

 

The Date Dimension

Sk_DateDateYearYear_Month
202001012020-01-0120202020M01
202001022020-01-0220202020M01
............
202112302021-12-3020212021M12
202112312021-12-3120212021M12

 

The Fact Table

Sk_DataSk_Employee
2020110112689
2020110212689
2020110312689
......
2021020112690
2021020212690
2021020312690
......
2021020812691
2021020912691
2021021012691
......
2021051012692
2021051112692
2021051212692
......

 

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_Name2020M112020M122021M012021M022021M032021M042021M05
+ Jonh Doe 1112111
  + 5682   1111
      Effective   1111
  + 1008631111   
      Temporary1111   
 Total1112111

 

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_Name2020M112020M122021M012021M022021M032021M042021M05
+ Jonh Doe 1111111
  + 5682   1111
      Effective   1111
  + 100863111    
      Temporary111    
 Total1111111

 

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

View solution in original post

8 REPLIES 8
TheoC
Memorable Member
Memorable Member

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

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Anonymous
Not applicable

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
Memorable Member
Memorable Member

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.

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Anonymous
Not applicable

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

 

TheoC
Memorable Member
Memorable Member

@Anonymous did it end up working?

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Anonymous
Not applicable

@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_EmployeeEmployee_IdEmployee_NameEnd_DateLast_Month_Date
12689100863Jonh Doe 2021-02-012021-02-28
12690100863Jonh Doe2099-01-012099-01-31
126915682Jonh Doe2021-05-102021-05-31
126925682Jonh Doe2099-01-012099-01-31
Anonymous
Not applicable

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

 

View solution in original post

TheoC
Memorable Member
Memorable Member

@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. 

 

If I have posted a response that resolves your question, please accept it as a solution to formally close the post.

Also, if you are as passionate about Power BI, DAX and data as I am, please feel free to reach out if you have any questions, queries, or if you simply want to connect and talk to another data geek!

Want to connect?  www.linkedin.com/in/theoconias

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors