Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
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.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Thank You So Much...Its working
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |