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 have an interesting situation in which I need to develop running (moving) totals without Time Intelligence.
I do not have a special Date dimension table in my Power BI data model.
See the source table below:
I have a measure:
TotalLaptops = SUM(TableSource[Laptops])
I need to develop a running total, which is only partially date related. I have DeliveryDates that spread across years. I need to get the cumulative sum taking each of the 3 groups to account without worrying about how the dates spread across months, or even years. However, the order of the dates is important for the cumulative total (obviously).
I need a new measure called [LaptopRunningTotal].
Any idea on how to develop the new measure?
LaptopRunningTotal = CALCULATE (
[TotalLaptops],
FILTER (
ALL(TableSource),
TableSource[DeliveryDate] <= MAX(TableSource[DeliveryDate])
&&
TableSource[Group1] = MAX(TableSource[Group1])
&&
TableSource[Group2] = MAX(TableSource[Group2])
&&
TableSource[Group3] = MAX(TableSource[Group3])
)
)
Is this one above okay ? Am not sure whether the above is fool-proof, please let me know.
Solved! Go to Solution.
@snph1777 , This one seem fine. If you want filters to work use allselected
LaptopRunningTotal = CALCULATE (
[TotalLaptops],
FILTER (
allselected (TableSource),
TableSource[DeliveryDate] <= MAX(TableSource[DeliveryDate])
&&
TableSource[Group1] = MAX(TableSource[Group1])
&&
TableSource[Group2] = MAX(TableSource[Group2])
&&
TableSource[Group3] = MAX(TableSource[Group3])
)
)
@snph1777 , This one seem fine. If you want filters to work use allselected
LaptopRunningTotal = CALCULATE (
[TotalLaptops],
FILTER (
allselected (TableSource),
TableSource[DeliveryDate] <= MAX(TableSource[DeliveryDate])
&&
TableSource[Group1] = MAX(TableSource[Group1])
&&
TableSource[Group2] = MAX(TableSource[Group2])
&&
TableSource[Group3] = MAX(TableSource[Group3])
)
)
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |