cancel
Showing results for 
Search instead for 
Did you mean: 
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

Accepted Solutions
scottsen Memorable Member
Memorable Member

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

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

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

Can you post some sample data to play with?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!
Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

scottsen Memorable Member
Memorable Member

Re: Calculate measure Value from Begining of Data to Start of Slicer Date and End of Slicer 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 :))

prashantpattnai
Frequent Visitor

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

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 

scottsen Memorable Member
Memorable Member

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

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

prashantpattnai
Frequent Visitor

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

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

scottsen Memorable Member
Memorable Member

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

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?

prashantpattnai
Frequent Visitor

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

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
scottsen Memorable Member
Memorable Member

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

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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors