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
prashantpattnai
Frequent Visitor

Calculate measure Value from Begining of Data to Start of Slicer Date and End of Slicer Date

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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

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

Greg_Deckler
Super User
Super User

Can you post some sample data to play with?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Thank You so much for quick check. Below is teh sampel data to use.

 

EmployeeCodeDateOfJoin
12006-09-18
22006-11-20
32006-12-01
42006-12-11
52007-04-12
62007-04-20
72007-08-16
82007-09-03
92007-09-03
102008-03-17
112009-12-07
122010-04-15
132010-11-17
142011-04-11
152011-07-11
162011-11-07
172012-01-18
182012-05-20
192012-10-31
362013-01-02

 

Sample Outcome 

Calendar Date Slicer  
01-01-2010to 31-12-2012
[Count Till Start of Period] 11 
[Count Till End of Period]19 

 

Thank You 

Anonymous
Not applicable

[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

Anonymous
Not applicable

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

Dateyearmonthday
09-01-20062006Sep1
09-02-20062006Sep2
09-03-20062006Sep3
09-04-20062006Sep4
09-05-20062006Sep5
09-06-20062006Sep6
09-07-20062006Sep7
09-08-20062006Sep8
09-09-20062006Sep9
09-10-20062006Sep10
Anonymous
Not applicable

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

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.