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

1 ACCEPTED SOLUTION

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

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

Proud to be a Datanaut!

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

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.

 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

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)

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

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?

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

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

Announcements

#### Announcing the New Spanish Forum

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

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

#### ‘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