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!
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!
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 |
---|---|
47 | |
22 | |
20 | |
15 | |
13 |
User | Count |
---|---|
49 | |
41 | |
39 | |
19 | |
19 |