Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I'm trying to find a running total for orders packed within a certain PromiseDate "bucket". The dates may be in any order in my table, but the way I'm thinking they are ordered by PromiseDate in descending order and PackDate in ascending order within the PromiseDate. Looking at the records with a PromiseDate of 6/10/2023 with the expected running total will give an idea of what I'm working with.
Also, something else I've come across is if orders have the same pack date it groups those together, so I guess something else like OrderNum sort has to be taken into consideration. OrderNum is unique.
I've searched a lot and see there are many topics on this subject, but I haven't been able to get this to work (RANKX, Quick Measure - Running Total, DAX Calculate, etc). Thank you in advanced for any help or insight.
PromiseDate | OrderNum | PackDate | PackLbs | ExpectedRunning |
6/17/2023 | 1485 | 4/28/2023 | 1000 | 1000 |
6/10/2023 | 3847 | 5/2/2023 | 2000 | 3000 |
6/10/2023 | 4120 | 5/1/2023 | 1000 | 1000 |
6/10/2023 | 2587 | 5/4/2023 | 2000 | 5000 |
6/3/2023 | 2285 | 4/29/2023 | 1000 | 1000 |
6/3/2023 | 5874 | 4/29/2023 | 3000 | 4000 |
5/27/2023 | 5668 | 4/30/2023 | 5000 | 5000 |
Solved! Go to Solution.
Hi,
I assume the expected outcome is a measure, not a calculated column.
Please check the below picture and the attached pbix file.
Expected outcome running total: =
IF (
HASONEVALUE ( Data[PromiseDate] ),
CALCULATE (
SUM ( Data[PackLbs] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALL ( Data ),
Data[PromiseDate],
Data[OrderNum],
Data[PackDate],
Data[PackLbs]
),
ORDERBY ( Data[PackDate], ASC, Data[OrderNum], ASC ),
,
PARTITIONBY ( Data[PromiseDate] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I assume the expected outcome is a measure, not a calculated column.
Please check the below picture and the attached pbix file.
Expected outcome running total: =
IF (
HASONEVALUE ( Data[PromiseDate] ),
CALCULATE (
SUM ( Data[PackLbs] ),
WINDOW (
1,
ABS,
0,
REL,
SUMMARIZE (
ALL ( Data ),
Data[PromiseDate],
Data[OrderNum],
Data[PackDate],
Data[PackLbs]
),
ORDERBY ( Data[PackDate], ASC, Data[OrderNum], ASC ),
,
PARTITIONBY ( Data[PromiseDate] )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Thanks so much! I had not seen the WINDOW function being used yet. I had to upgrade to the latest Desktop version of PowerBI for it to work.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
83 | |
64 | |
60 | |
55 |
User | Count |
---|---|
179 | |
108 | |
105 | |
71 | |
70 |