Forum Discussion
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 ...
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.
ERD
Community Champion
Community ChampionHi 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.
Thank You So Much...Its working