DAX Measure Average Count over Time

[azs]

Hi,

I am working with work order counts. Any work order is having a time stemp - opened, due to and closed - depending on "due to" and "closed" it can be evaluated, if the job was running overdue or is closed in time.

I am trying to get a measure, which is giving just the average number of overdue jobs over time.

for example:

A;B;C;D are opened on monday and due to by tuesday.

Monday: opened A;B;C;D --> overdue count 0

Tuesday: A is due to and closed as well; B&C&D are due to but not finished yet --> overdue count 3

Wednesday: B&C&D are closed --> overdue count 0

So the Average Overdue count these 3 days is "1".

I defined Overdue-Count as:

CALCULATE(

    COUNTA('Order'[Due to]),

    ALLSELECTED(Calender[Date])

and put on the filters "not closed" and "Due to"<"TODAY()".

This is working for current overdue jobs but it does not give me a historical average evaluation. I tried somehow with AverageX but still i don't now how to replace the "TODAY" with the time period, for which the overdue evaluation has to be done - "Calculate the overdues from every day by comparing "due to<=current date" AND "closed is no", then add up overdue counts per day and calculate the average over all time up to today".

thanks for any hints,

best regards,

azs

[Anonymous · Accepted solution]

Hi @azs 

Due to I don't know your data model, I build a sample to have a test.

Here I have two open days 2021/05/20 and 2021/05/24.

2021/05/20 Part:

2021/05/20: opened A;B;C;D --> overdue count 0

2021/05/21: C is due to and closed as well; A&B&D are due to but not finished yet --> overdue count 3

2021/05/22: A&B are overdue but closed; D is overdue but not closed --> overdue count 1

2021/05/23: D is overdue but closed --> overdue count 0

So the Averge count is (0+3+1+0)/ 4 = 1

2021/05/20 Part is the same like yours.

Measures:

Diff Day = DATEDIFF(MAX('Table'[Due to]),MAX('Table'[Close]),DAY)

Avg = 
VAR _Overdue = SUMX('Table',[Diff Day])
VAR _AllDaycount = DATEDIFF(MAX('Table'[Open]),MAX('Table'[Close]),DAY) +1
VAR _Avg = DIVIDE(_Overdue,_AllDaycount)
Return
_Avg

Result is as below.

Best Regards,

Rico Zhou

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

[amitchandak]

@azs , Something like this

COUNTA('Order'[Due to]),filter('Order', 'Order'[Status] ="not closed" && 'Order'[due to] <today()))

or

COUNTA('Order'[Due to]),filter('Order', 'Order'[Status] ="not closed" && 'Order'[due to] <today()), ALLSELECTED(Calender[Date]))

or

COUNTA('Order'[Due to]),filter('Order', 'Order'[Status] ="not closed" && 'Order'[due to] <today()), ALL(Calender[Date]))