Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |