Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
Help me with the formula to calculate measure value for below scenarion.
I have employee data where date of joining begins from say 2000-01-01 and continues till date. There is a Calendar Date slicer in my report, Calendar Date is linked with Date Of Join. Need 2 measures [Count Till Start of Period] and [Count Till End of Period].
If calender date slicer is set to 2015-01-01 to 2015-12-31, calculation should be as below:
[Count Till Start of Period] = Count of rows starting 2000-01-01 till 2015-01-01
[Count Till End of Period] = Count of rows starting 2000-01-01 till 2015-12-31
Appreciate your help on this.
Solved! Go to Solution.
Oh. I feel you actually do NOT want a relationship between calendar and employee tables. As then you are filtering down to ONLY employees with a join date in the current month.
We can probably "remove" that relationship via DAX / ALL( ) as well. Maybe that is a better plan? Maybe try...
[Count Till Start] = CALCULATE(COUNTROWS(MyEmployee), ALL(MyCalendar), FILTER(MyEmployee, MyEmployee[DateOfJoin] <= MIN(MyCalendar[Date])))
DATEDIFF https://msdn.microsoft.com/en-us/library/dn802538.aspx can be used to calculate the number of days between 2 dates.
I would think something like:
[Days Since Start] := SUMX(Employee, DATEDIFF(Employee[StartDate], MIN(Calendar[Date]))
(Edit: Given response below, I am way off, ignore me :))
Can you post some sample data to play with?
Thank You so much for quick check. Below is teh sampel data to use.
EmployeeCode | DateOfJoin |
1 | 2006-09-18 |
2 | 2006-11-20 |
3 | 2006-12-01 |
4 | 2006-12-11 |
5 | 2007-04-12 |
6 | 2007-04-20 |
7 | 2007-08-16 |
8 | 2007-09-03 |
9 | 2007-09-03 |
10 | 2008-03-17 |
11 | 2009-12-07 |
12 | 2010-04-15 |
13 | 2010-11-17 |
14 | 2011-04-11 |
15 | 2011-07-11 |
16 | 2011-11-07 |
17 | 2012-01-18 |
18 | 2012-05-20 |
19 | 2012-10-31 |
36 | 2013-01-02 |
Sample Outcome
Calendar Date Slicer | ||
01-01-2010 | to | 31-12-2012 |
[Count Till Start of Period] | 11 | |
[Count Till End of Period] | 19 |
Thank You
[Count Till Start] := CALCULATE(COUNTROWS(Employee), FILTER(Employee, Employee[DateOfJoin] <= MIN(Calendar[Date]))
[Count Till End] := CALCULATE(COUNTROWS(Employee), FILTER(Employee, Employee[DateOfJoin] <= MAX(Calendar[Date]))
(edit: make syntax valid)
Hi Scott,
Thanks for the response. Somehow this formula is not giving desired result.
If you see the above sample data set, I'm expecting [Count Till Start] = 11 and [Count Till End] = 19.
But this formula,
[Count Till Start] = CALCULATE(COUNTROWS(MyEmployee), FILTER(MyEmployee, MyEmployee[DateOfJoin] <= MIN(MyCalendar[Date]))) is returning BLANK
[Count Till End] = CALCULATE(COUNTROWS(MyEmployee), FILTER(MyEmployee, MyEmployee[DateOfJoin] <= MAX(MyCalendar[Date]))) is returning 8 😞
Thank You
Am I correctly assuming that the date range is specified via a filtering the MyCalendar table?
[Count Till Start] = CALCULATE(COUNTROWS(MyEmployee), FILTER(MyEmployee, MyEmployee[DateOfJoin] <= MIN(MyCalendar[Date])))
Count the rows of MyEmployee, filtering to where MyEmployee[DateOfJoin] is equal or before the min date in the current MyCalendar context.
Man, that "reads" really well... what am I missing?
Yes, your assumption is correct. Below is sample data of MyCalendar table .
MyEmployee and MyCalendar are connected referenced as MyCalendar.Date = MyEmployee.DateOfJoin
Date | year | month | day |
09-01-2006 | 2006 | Sep | 1 |
09-02-2006 | 2006 | Sep | 2 |
09-03-2006 | 2006 | Sep | 3 |
09-04-2006 | 2006 | Sep | 4 |
09-05-2006 | 2006 | Sep | 5 |
09-06-2006 | 2006 | Sep | 6 |
09-07-2006 | 2006 | Sep | 7 |
09-08-2006 | 2006 | Sep | 8 |
09-09-2006 | 2006 | Sep | 9 |
09-10-2006 | 2006 | Sep | 10 |
Oh. I feel you actually do NOT want a relationship between calendar and employee tables. As then you are filtering down to ONLY employees with a join date in the current month.
We can probably "remove" that relationship via DAX / ALL( ) as well. Maybe that is a better plan? Maybe try...
[Count Till Start] = CALCULATE(COUNTROWS(MyEmployee), ALL(MyCalendar), FILTER(MyEmployee, MyEmployee[DateOfJoin] <= MIN(MyCalendar[Date])))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
122 | |
101 | |
71 | |
61 |