The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi!
I'm very new to Power Bi, but I'm hoping to take some suggestions on how to go about this.
Essentially I’m trying to create a custom column to essentially calculate the previous hour’s remaining qty + next hour’s allocated qty to get a snapshot of the total volume for each hour, but I can’t figure out the formula.
So for example, the day starts at midnight (12:00 AM, hour 0) and I have 598 units drop but only picked 29, I need my 1:00 AM new total to be 569 remaining from 12:00 AM + the additional 335 units that were allocated at 1 am which would 904 units total. Then we picked 351 between 1-2am, so for 2am it would take the remaining 553 (904-351 picks) + the 285 that are allocated at 2am and so on and so forth.
Any tips on how to go about this?
Solved! Go to Solution.
You could try creating a column like
Running Total =
VAR currentDate = 'Table'[DATE_REF]
VAR currentHour = 'Table'[Hour_ref]
VAR totalAllocated =
CALCULATE (
SUM ( 'Table'[Cart Allocated] ),
ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
'Table'[Hour_ref] <= currentHour
)
VAR totalPicked =
CALCULATE (
SUM ( 'Table'[Cart Picks] ),
ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
'Table'[Hour_ref] < currentHour
)
RETURN
totalAllocated - totalPicked
You could try creating a column like
Running Total =
VAR currentDate = 'Table'[DATE_REF]
VAR currentHour = 'Table'[Hour_ref]
VAR totalAllocated =
CALCULATE (
SUM ( 'Table'[Cart Allocated] ),
ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
'Table'[Hour_ref] <= currentHour
)
VAR totalPicked =
CALCULATE (
SUM ( 'Table'[Cart Picks] ),
ALLEXCEPT ( 'Table', 'Table'[DATE_REF] ),
'Table'[Hour_ref] < currentHour
)
RETURN
totalAllocated - totalPicked
that worked perfectly! The only thing I had to change was in the VAR totalPicked the "<" sign to "<="
Thanks so much!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
33 | |
20 | |
16 | |
15 |
User | Count |
---|---|
56 | |
50 | |
36 | |
35 | |
31 |