cancel
Showing results for
Did you mean:
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_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_EmployeeFROM    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.

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_EmployeeFROM    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`

8 REPLIES 8
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!

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

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!

Anonymous
Not applicable

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

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!

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_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
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_EmployeeFROM    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`

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!

Announcements