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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Super User
Super User

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 )
)
)

 

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.

 

@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

 

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.