Forum Discussion

Fathopes's avatar
Fathopes
Frequent Visitor
4 years ago
Solved

Return Previos Collection Date and Value

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 DateOUTLET NAMETotal KG Collected
22/5/2021 0:00BAMBOO GARDEN80.19
18/6/2021 0:00BAMBOO GARDEN0
19/7/2021 0:00BAMBOO GARDEN0
20/8/2021 0:00BAMBOO GARDEN0
16/6/2021 0:0021 SEAFOOD17
8/7/2021 0:0021 SEAFOOD0
23/7/2021 0:0021 SEAFOOD0
2/9/2021 0:0021 SEAFOOD0
30/9/2021 0:0021 SEAFOOD34
14/10/2021 0:0021 SEAFOOD0

 

the trip have zero collection

Zero Collection DateOUTLET NAMETotal KG CollectedPrevious Collection DatePrevious Quantity CollectedDays Between Previous Collection and Current Zero Collection
2/9/2021 0:0021 SEAFOOD0   
14/10/2021 0:0021 SEAFOOD0   
8/7/2021 0:0021 SEAFOOD0   
23/7/2021 0:0021 SEAFOOD0   
18/6/2021 0:00BAMBOO GARDEN0   
19/7/2021 0:00BAMBOO GARDEN0   
20/8/2021 0:00BAMBOO GARDEN0   

 

Thanks and Regards,

Hopes

  • ERD's avatar
    ERD
    4 years ago

    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.

2 Replies

  • ERD's avatar
    ERD
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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.