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
Anonymous
Not applicable

custom date, days diff

Hello, 

I am trying to use custom date and calculate days diff
 
table HRMetrics columns HireDate, EffectiveDate, worker
 
table custom columns date
 
Problem: 
 
I expected this measure to return single row for every worker but instead it is returning many rows
 
Measure =
VAR _Date = SELECTEDVALUE('Custom'[Date])
VAR _HireDate = SELECTEDVALUE(HrMetrics[HireDate])
VAR _EffectiveDate = IF(SELECTEDVALUE(HrMetrics[EffectiveDate]) <_Date, _Date, SELECTEDVALUE(HrMetrics[EffectiveDate]))
RETURN IF(_HireDate > _Date, Blank(), DATEDIFF(_HireDate, _EffectiveDate, DAY))
1 ACCEPTED SOLUTION

Hi @Anonymous ,


Try the following formula, you can get the expected number of days.

M1 = 
VAR max_data =
    SELECTEDVALUE ( 'Custom'[Date] )
VAR _hiredata =
    MAX ( HrMetrics[Hiredate] )
VAR _effdata =
    IF (
        MAX ( HrMetrics[Effectivedate] ) < max_data,
        MAX ( HrMetrics[Effectivedate] ),
        max_data
    )
RETURN
    IF ( max_data >= _hiredata, DATEDIFF ( _hiredata, _effdata, DAY ), BLANK () )

v-henryk-mstf_0-1614060546143.png

 


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-henryk-mstf
Community Support
Community Support

Hi @Anonymous ,


It may be related to your table relationship or the use of Filter in the report. Can you show the results you expect through excel, and also hope to provide test data, so that I can help you answer as soon as possible.

 

Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Worker,HireDate,EffectiveDate
1,1/1/1994,1/1/2000
2,1/1/1995,1/1/2001
3,1/1/1996,1/4/2020
4,1/1/1997,1/1/2003
5,1/1/1998,1/1/2004
6,1/1/1999,1/1/2005
7,1/1/2000,1/1/2006
8,1/1/2001,1/7/2020
9,1/1/2002,1/1/2008
10,1/1/2003,1/1/2009
11,1/1/2004,1/1/2010
12,1/1/2005,1/1/2011
13,1/1/2006,1/1/2012
14,1/1/2007,1/1/2013
15,1/1/2008,1/15/2014
16,1/1/2009,1/1/2015
17,1/1/2010,1/1/2016
18,1/1/2011,1/1/2017
19,1/1/2012,1/1/2018
20,1/1/2013,1/1/2000
21,1/1/2014,1/1/2000
22,1/1/2015,1/1/2001
23,1/1/2016,1/4/2020
24,1/1/2017,1/1/2003
25,1/1/2018,1/1/2004
26,1/1/2019,1/1/2005
27,1/1/2020,1/1/2006
28,1/1/2021,1/7/2020
29,1/1/1994,1/1/2008
30,1/1/1995,1/1/2009
31,1/1/1996,1/1/2010
32,1/1/1997,1/1/2011
33,1/1/1998,1/1/2012
34,1/1/1999,1/1/2013
35,1/1/2000,1/15/2014
36,1/1/2001,1/1/2015
37,1/1/2002,1/1/2016
38,1/1/2003,1/1/2017
39,1/1/2004,1/1/2018
40,1/1/2005,1/1/2000
41,1/1/2006,1/1/2000
42,1/1/2007,1/1/2001
43,1/1/2008,1/4/2020
44,1/1/2009,1/1/2003
45,1/1/2010,1/1/2004
46,1/1/2011,1/1/2005
47,1/1/2012,1/1/2006
48,1/1/2013,1/7/2020
49,1/1/2014,1/1/2008
50,1/1/2015,1/1/2009
51,1/1/2016,1/1/2010
52,1/1/2017,1/1/2011
53,1/1/2018,1/1/2012
54,1/1/2019,1/1/2013
55,1/1/2020,1/15/2014
56,1/1/2021,1/1/2015
57,1/1/1994,1/1/2016
58,1/1/1995,1/1/2017
59,1/1/1996,1/1/2018
60,1/1/1997,1/1/2000
61,1/1/1998,1/1/2000
62,1/1/1999,1/1/2001
63,1/1/2000,1/4/2020
64,1/1/2001,1/1/2003
65,1/1/2002,1/1/2004
66,1/1/2003,1/1/2005
67,1/1/2004,1/1/2006
68,1/1/2005,1/7/2020
69,1/1/2006,1/1/2008
70,1/1/2007,1/1/2009
71,1/1/2008,1/1/2010
72,1/1/2009,1/1/2011
73,1/1/2010,1/1/2012
74,1/1/2011,1/1/2013
75,1/1/2012,1/15/2014
76,1/1/2013,1/1/2015
77,1/1/2014,1/1/2016
78,1/1/2015,1/1/2017
79,1/1/2016,1/1/2018
80,1/1/2017,1/1/2000
81,1/1/2018,1/1/2000
82,1/1/2019,1/1/2001
83,1/1/2020,1/4/2020
84,1/1/2021,1/1/2003
85,1/1/1994,1/1/2004
86,1/1/1995,1/1/2005
87,1/1/1996,1/1/2006
88,1/1/1997,1/7/2020
89,1/1/1998,1/1/2008
90,1/1/1999,1/1/2009
91,1/1/2000,1/1/2010
92,1/1/2001,1/1/2011
93,1/1/2002,1/1/2012
94,1/1/2003,1/1/2013
95,1/1/2004,1/15/2014
96,1/1/2005,1/1/2015
97,1/1/2006,1/1/2016
98,1/1/2007,1/1/2017
99,1/1/2008,1/1/2018
100,1/1/2009,1/1/2000
101,1/1/2010,1/1/2000
102,1/1/2011,1/1/2001
103,1/1/2012,1/4/2020
104,1/1/2013,1/1/2003

 

Date
1/1/2020
1/2/2020
1/3/2020
1/4/2020
1/5/2020
1/6/2020
1/7/2020
1/8/2020
1/9/2020
1/10/2020
1/11/2020
1/12/2020
1/13/2020
1/14/2020
1/15/2020
1/16/2020
1/17/2020
1/18/2020
1/19/2020
1/20/2020
1/21/2020
1/22/2020
1/23/2020
1/24/2020
1/25/2020
1/26/2020
1/27/2020
1/28/2020
1/29/2020
1/30/2020
1/31/2020
2/1/2020
2/2/2020
2/3/2020
2/4/2020
2/5/2020
2/6/2020
2/7/2020
2/8/2020
2/9/2020
2/10/2020
2/11/2020
2/12/2020
2/13/2020
2/14/2020
2/15/2020
2/16/2020
2/17/2020
2/18/2020
2/19/2020
2/20/2020
2/21/2020
2/22/2020
2/23/2020
2/24/2020
2/25/2020
2/26/2020
2/27/2020
2/28/2020
2/29/2020
3/1/2020
3/2/2020
3/3/2020
3/4/2020
3/5/2020
3/6/2020
3/7/2020
3/8/2020
3/9/2020
3/10/2020
3/11/2020
3/12/2020
3/13/2020
3/14/2020
3/15/2020
3/16/2020
3/17/2020
3/18/2020
3/19/2020
3/20/2020
3/21/2020
3/22/2020
3/23/2020
3/24/2020
3/25/2020
3/26/2020
3/27/2020
3/28/2020
3/29/2020
3/30/2020
3/31/2020
4/1/2020
4/2/2020
4/3/2020
4/4/2020
4/5/2020
4/6/2020
4/7/2020
4/8/2020
4/9/2020
4/10/2020
4/11/2020
4/12/2020
4/13/2020
4/14/2020
4/15/2020
4/16/2020
4/17/2020
4/18/2020
4/19/2020
4/20/2020
4/21/2020
4/22/2020
4/23/2020
4/24/2020
4/25/2020
4/26/2020
4/27/2020
4/28/2020
4/29/2020
4/30/2020
5/1/2020
5/2/2020
5/3/2020
5/4/2020
5/5/2020
5/6/2020
5/7/2020
5/8/2020
5/9/2020
5/10/2020
5/11/2020
5/12/2020
5/13/2020
5/14/2020
5/15/2020
5/16/2020
5/17/2020
5/18/2020
5/19/2020
5/20/2020
5/21/2020
5/22/2020
5/23/2020
5/24/2020
5/25/2020
5/26/2020
5/27/2020
5/28/2020
5/29/2020
5/30/2020
5/31/2020
6/1/2020
6/2/2020
6/3/2020
6/4/2020
6/5/2020
6/6/2020
6/7/2020
6/8/2020
6/9/2020
6/10/2020
6/11/2020
6/12/2020
6/13/2020
6/14/2020
6/15/2020
6/16/2020
6/17/2020
6/18/2020
6/19/2020
6/20/2020
6/21/2020
6/22/2020
6/23/2020
6/24/2020
6/25/2020
6/26/2020
6/27/2020
6/28/2020
6/29/2020
6/30/2020
7/1/2020
7/2/2020
7/3/2020
7/4/2020
7/5/2020
7/6/2020
7/7/2020
7/8/2020
7/9/2020
7/10/2020
7/11/2020
7/12/2020
7/13/2020
7/14/2020
7/15/2020
7/16/2020
7/17/2020
7/18/2020
7/19/2020
7/20/2020
7/21/2020
7/22/2020
7/23/2020
7/24/2020
7/25/2020
7/26/2020
7/27/2020
7/28/2020
7/29/2020
7/30/2020
7/31/2020
8/1/2020
8/2/2020
8/3/2020
8/4/2020
8/5/2020
8/6/2020
8/7/2020
8/8/2020
8/9/2020
8/10/2020
8/11/2020
8/12/2020
8/13/2020
8/14/2020
8/15/2020
8/16/2020
8/17/2020
8/18/2020
8/19/2020
8/20/2020
8/21/2020
8/22/2020
8/23/2020
8/24/2020
8/25/2020
8/26/2020
8/27/2020
8/28/2020
8/29/2020
8/30/2020
8/31/2020
9/1/2020
9/2/2020
9/3/2020
9/4/2020
9/5/2020
9/6/2020
9/7/2020
9/8/2020
9/9/2020
9/10/2020
9/11/2020
9/12/2020
9/13/2020
9/14/2020
9/15/2020
9/16/2020
9/17/2020
9/18/2020
9/19/2020
9/20/2020
9/21/2020
9/22/2020
9/23/2020
9/24/2020
9/25/2020
9/26/2020
9/27/2020
9/28/2020
9/29/2020
9/30/2020
10/1/2020
10/2/2020
10/3/2020
10/4/2020
10/5/2020
10/6/2020
10/7/2020
10/8/2020
10/9/2020
10/10/2020
10/11/2020
10/12/2020
10/13/2020
10/14/2020
10/15/2020
10/16/2020
10/17/2020
10/18/2020
10/19/2020
10/20/2020
10/21/2020
10/22/2020
10/23/2020
10/24/2020
10/25/2020
10/26/2020
10/27/2020
10/28/2020
10/29/2020
10/30/2020
10/31/2020
11/1/2020
11/2/2020
11/3/2020
11/4/2020
11/5/2020
11/6/2020
11/7/2020
11/8/2020
11/9/2020
11/10/2020
11/11/2020
11/12/2020
11/13/2020
11/14/2020
11/15/2020
11/16/2020
11/17/2020
11/18/2020
11/19/2020
11/20/2020
11/21/2020
11/22/2020
11/23/2020
11/24/2020
11/25/2020
11/26/2020
11/27/2020
11/28/2020
11/29/2020
11/30/2020
12/1/2020
12/2/2020
12/3/2020
12/4/2020
12/5/2020
12/6/2020
12/7/2020
12/8/2020
12/9/2020
12/10/2020
12/11/2020
12/12/2020
12/13/2020
12/14/2020
12/15/2020
12/16/2020
12/17/2020
12/18/2020
12/19/2020
12/20/2020
12/21/2020
12/22/2020
12/23/2020
12/24/2020
12/25/2020
12/26/2020
12/27/2020
12/28/2020
12/29/2020
12/30/2020
12/31/2020
1/1/2021
1/2/2021
1/3/2021
1/4/2021
1/5/2021
1/6/2021
1/7/2021
1/8/2021
1/9/2021
1/10/2021
1/11/2021
1/12/2021
1/13/2021
1/14/2021
1/15/2021
1/16/2021
1/17/2021
1/18/2021
1/19/2021
1/20/2021
1/21/2021
1/22/2021
1/23/2021
1/24/2021
1/25/2021
1/26/2021
1/27/2021
1/28/2021
1/29/2021
1/30/2021
1/31/2021
2/1/2021
2/2/2021
2/3/2021
2/4/2021
2/5/2021
2/6/2021
2/7/2021
2/8/2021
2/9/2021

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Hi @Anonymous ,


Try the following formula, you can get the expected number of days.

M1 = 
VAR max_data =
    SELECTEDVALUE ( 'Custom'[Date] )
VAR _hiredata =
    MAX ( HrMetrics[Hiredate] )
VAR _effdata =
    IF (
        MAX ( HrMetrics[Effectivedate] ) < max_data,
        MAX ( HrMetrics[Effectivedate] ),
        max_data
    )
RETURN
    IF ( max_data >= _hiredata, DATEDIFF ( _hiredata, _effdata, DAY ), BLANK () )

v-henryk-mstf_0-1614060546143.png

 


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@Anonymous , Try like

 

Measure =
VAR _Date = max('Custom'[Date])
VAR _HireDate = max(HrMetrics[HireDate])
VAR _EffectiveDate = IF(max(HrMetrics[EffectiveDate]) <_Date, _Date, max(HrMetrics[EffectiveDate]))
RETURN IF(_HireDate > _Date, Blank(), DATEDIFF(_HireDate, _EffectiveDate, DAY))

 

 

a column
New column =
VAR _Date = ('Custom'[Date])
VAR _HireDate = (HrMetrics[HireDate])
VAR _EffectiveDate = IF((HrMetrics[EffectiveDate]) <_Date, _Date, (HrMetrics[EffectiveDate]))
RETURN IF(_HireDate > _Date, Blank(), DATEDIFF(_HireDate, _EffectiveDate, DAY))

Anonymous
Not applicable

Hello @amitchandak 

that dint work (measure) 

a column wont work in this scenario because Custom[Date] has many  values and I'm using a slicer to  select just 1 value.

@Anonymous , Is there a slicer on the page. if yes on which date?

Anonymous
Not applicable

on Custom['Date'] slecting a single 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.