Forum Discussion

jillw's avatar
jillw
Frequent Visitor
6 years ago

Error with Daily Average Days Open Measure

I'm currently using this measure to calculate the average days open for records that were open based on any given date. The measure is working for the past few months of data but I am finding some errors when looking further into the past. Where the data should be going up by 1 for each passing day that records aren't closed or opened, it is showing static values.

 

Here is the measure I am using:

 

Daily Avg Days Open Error Data = 
VAR _currentDate = SELECTEDVALUE('Dates'[Date])
RETURN (
AVERAGEX(
filter('Error Data'
, 'Error Data'[Date Created] <= _currentDate
&& ('Error Data'[DateClosed] > _currentDate || ISBLANK('Error Data'[DateClosed]))
)
,var _closedDate = IF(ISBLANK('Error Data'[DateClosed]),_currentDate,'Error Data'[DateClosed])
return _closedDate - 'Error Data'[Date Created]
)
)
 
This is the record data:
 
Record ID#Date CreatedDateClosed
31898/19/20193/31/2020
31887/9/20192/26/2020
31866/19/20193/3/2020
31846/12/20193/31/2020
31836/7/20193/11/2020
31815/30/20193/6/2020
31795/29/20193/3/2020
31785/29/20199/13/2019
31765/21/20193/3/2020
31744/29/20198/7/2019
31724/17/201912/12/2019
31714/9/20193/4/2020
31704/2/201911/4/2019
31693/27/201911/5/2019
31683/22/201911/13/2019
31663/4/201912/6/2019
31652/27/20199/13/2019
31632/12/201911/12/2019
31622/12/201911/5/2019
31612/12/201912/6/2019
31602/12/201910/25/2019
31592/12/201910/24/2019
31582/12/201910/24/2019
31572/11/201910/24/2019
31562/5/201911/13/2019
31541/17/201911/11/2019
315211/26/201811/25/2019
315311/26/201811/12/2019
315111/26/201811/11/2019
315011/21/20188/23/2019
31289/25/20189/6/2019
310212/20/201711/11/2019

 

And these are the results I am getting, vs the results I should be getting based out of excel:

 

DatePowerBIExcel
7/2/2019280.4132.3333
7/3/2019280.4133.3333
7/4/2019280.4134.3333
7/5/2019280.4135.3333
7/6/2019280.4136.3333
7/7/2019280.4137.3333
7/8/2019280.4138.3333
7/9/2019278.8387139.3333
7/10/2019278.8387135.8387
7/11/2019278.8387136.8387
7/12/2019278.8387137.8387
7/13/2019278.8387138.8387
7/14/2019278.8387139.8387
7/15/2019278.8387140.8387
7/16/2019278.8387141.8387
7/17/2019278.8387142.8387
7/18/2019278.8387143.8387
7/19/2019278.8387144.8387
7/20/2019278.8387145.8387
7/21/2019278.8387146.8387
7/22/2019278.8387147.8387
7/23/2019278.8387148.8387
7/24/2019278.8387149.8387
7/25/2019278.8387150.8387
7/26/2019278.8387151.8387
7/27/2019278.8387152.8387
7/28/2019278.8387153.8387
7/29/2019278.8387154.8387
7/30/2019278.8387155.8387
7/31/2019278.8387156.8387
8/1/2019278.8387157.8387
8/2/2019278.8387158.8387
8/3/2019278.8387159.8387
8/4/2019278.8387160.8387
8/5/2019278.8387161.8387
8/6/2019278.8387162.8387
8/7/2019284.8163.8387
8/8/2019284.8166.9667
8/9/2019284.8167.9667
8/10/2019284.8168.9667
8/11/2019284.8169.9667
8/12/2019284.8170.9667
8/13/2019284.8171.9667
8/14/2019284.8172.9667
8/15/2019284.8173.9667
8/16/2019284.8174.9667
8/17/2019284.8175.9667
8/18/2019284.8176.9667
8/19/2019282.871177.9667
8/20/2019282.871173.2258
8/21/2019282.871174.2258
8/22/2019282.871175.2258
8/23/2019283.1333176.2258
8/24/2019283.1333173.9333
8/25/2019283.1333174.9333
8/26/2019283.1333175.9333
8/27/2019283.1333176.9333
8/28/2019283.1333177.9333
8/29/2019283.1333178.9333
8/30/2019283.1333179.9333

 

I have a date table containing a value for each day of the year for the past 5 years. There is no active relationship between the record table and the date table.

Need Help

2 Replies