Return Previos Collection Date and Value

[Fathopes]

Hi All,

I have one table below consist of Trips of outlet with collection. I would like to return the table like below where the trip have zero collection as per my applied filter is 0 for total KG collected. Based on the table below, I would like to return the previos date collection, previous qty collected and days in between. This will be very helpful for me if there is any dax formula that able return below data

Trips of Outlet with Collection

Collection Date	OUTLET NAME	Total KG Collected
22/5/2021 0:00	BAMBOO GARDEN	80.19
18/6/2021 0:00	BAMBOO GARDEN	0
19/7/2021 0:00	BAMBOO GARDEN	0
20/8/2021 0:00	BAMBOO GARDEN	0
16/6/2021 0:00	21 SEAFOOD	17
8/7/2021 0:00	21 SEAFOOD	0
23/7/2021 0:00	21 SEAFOOD	0
2/9/2021 0:00	21 SEAFOOD	0
30/9/2021 0:00	21 SEAFOOD	34
14/10/2021 0:00	21 SEAFOOD	0

the trip have zero collection

Zero Collection Date	OUTLET NAME	Total KG Collected	Previous Collection Date	Previous Quantity Collected	Days Between Previous Collection and Current Zero Collection
2/9/2021 0:00	21 SEAFOOD	0
14/10/2021 0:00	21 SEAFOOD	0
8/7/2021 0:00	21 SEAFOOD	0
23/7/2021 0:00	21 SEAFOOD	0
18/6/2021 0:00	BAMBOO GARDEN	0
19/7/2021 0:00	BAMBOO GARDEN	0
20/8/2021 0:00	BAMBOO GARDEN	0

Thanks and Regards,

Hopes

[ERD · Accepted solution]

Hi @Fathopes ,

You can use the measures below:

Total 0 KG Collected =
CALCULATE ( SUM ( T[Total KG Collected] ), T[Total KG Collected] = 0 )

Previous Collection Date =
VAR currentDate = SELECTEDVALUE ( T[Collection Date] )
RETURN
    CALCULATE (
        MAX ( T[Collection Date] ),
        T[Collection Date] < currentDate,
        T[Total KG Collected] <> 0
    )

Previous Quantity Collected =
VAR currentDate = SELECTEDVALUE ( T[Collection Date] )
VAR prevDate =
    CALCULATE (
        MAX ( T[Collection Date] ),
        T[Collection Date] < currentDate,
        T[Total KG Collected] <> 0
    )
RETURN
    CALCULATE ( MAX ( T[Total KG Collected] ), T[Collection Date] = prevDate )

Days Between =
VAR currentDate = SELECTEDVALUE ( T[Collection Date] )
VAR prevDate =
    CALCULATE (
        MAX ( T[Collection Date] ),
        T[Collection Date] < currentDate,
        T[Total KG Collected] <> 0
    )
RETURN
    DATEDIFF ( prevDate, currentDate, DAY )

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

[Fathopes]

Thank You So Much...Its working