Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |