how to create rolling count daily overdue orders

[Anonymous]

hi bi fanz

i'm trying to create a table where it has rolling overdue order

For e.g in the excel; its able to record the no. of overdue till the order was processed

Excel formula "COUNTIFS(DN created on,"<"&start date,pack status,"overdue",">"&completion date)+COUNTIFS(DN created on,"<"$start date,pack status,"overdue",completion date,empty/null)"

	21-Aug	22-Aug	23-Aug	24-Aug	25-Aug	26-Aug
	Fri	Sat	Sun	Mon	Tue	Wed
Pick Overdue	-	1	2	-	-	-
Pack Overdue	-	8	46	-	-	-

in my dummy date table i have tried using the dax to get the result but it seems to return the orders created for that day 

For column

"

overdue listing = CALCULATE(

COUNTAX(OBD,OBD[Pick Status]="Overdue" ),

FILTER(OBD,OBD[Created On]='Calendar'[Date]))"

For measures

CALCULATE(

COUNT(OBD[Pick Status]),

Filter( ALLEXCEPT(OBD,OBD[Pick Date]),OBD[Created On]<=SELECTEDVALUE(OBD[Pick Date])))

[v-yuaj-msft · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a calculated table and a measure as follows.

a calculated table

overdue_new =
CALENDAR (
    MIN ( 'countofoverdue'[order date created date] ),
    MAX ( 'countofoverdue'[order completed date] )
)

a measure

countif =
VAR x1 =
    CROSSJOIN ( 'countofoverdue', 'overdue_new' )
RETURN
    COUNTX (
        FILTER (
            x1,
            [Date] >= [order date created date] + 2
                && [Date] < [order completed date]
        ),
        [Order serial]
    ) + 0

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

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

[Anonymous]

your solution is lean and neat compared to the solution i came up with 🤣

[amitchandak]

@Anonymous , The information you have provided is not making the problem clear to me. Can you please explain with an example.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Appreciate your Kudos.

You can trying rolling measure with help from date tbale

Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max('Date'[Date]),-30,Day))

[Anonymous]

thanks for speedy response

basically; i'm trying to make the overdue orders continue to roll until the that its completed 

the condition is if the order is created on N date it should be completed by N+2; otherwise it will be overdue; 

Trying to make a area chart to show the trend of daily overdues 

raw data table

expected result table