Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi All,
I would like to ask your help to solve to following running total calculation problem as it is clearly above of my DAX knowledge.
Please, see this PBI file link for more details: RunningTotalDemo
I want to use running total from the actual month(presently 202107 and provided by m_ActualMonth measure) which base value is calculated on the following way:
ActualStock.Quantity + IODiff of 0BeforeActual + IODiff of actual month(202107). In other cases only the IODiff is counted. This calcualtion is provided by m_StockIODiffForRunningTotal measure:
m_StockIODiffForRunningTotal =
VAR ActualMonthValue =
SUM ( Material[ActualStock.Quantity] )
+ CALCULATE (
SUM ( StockInputOutput[IODiff] ),
StockInputOutput[YearMonthAndBefore] = "0BeforeActual"
)
+ SUM ( StockInputOutput[IODiff] )
VAR Result =
IF (
MIN ( StockInputOutput[YearMonthAndBefore] ) = [m_ActualMonth],
ActualMonthValue,
SUM ( StockInputOutput[IODiff] )
)
return Result
The first matrix shows the IODiff and the second shows the calculated running total in the value area:
The calculation of running total is ok, but there are some flaws, please see the color signs on the picture.
green: in case if the actual month(202107) as value then the m_StockIODiffForRunningTotal provides the correct value
red: if no value in the actual month, the m_StockIODiffForRunningTotal doesn't work
orange: the 0BeforeActual column is filtered because of the running total formula but it should be visible in the matrix.
My questions are:
- how can be calculated the "ActualStock.Quantity" + IODiff of 0BeforeActual if the actual month(202107) has no any value?
- how it is possible to calculate running total from the calculated actual month(202107) and show the 0BeforeActual column in the visual at once? Perhaps it is not possible if I use FILTER function in the running total measure:
m_RunningTotal =
calculate(
[m_StockIODiffForRunningTotal],
FILTER(
ALL(StockInputOutput[YearMonthAndBefore]),
StockInputOutput[YearMonthAndBefore] >= [m_ActualMonth] && StockInputOutput[YearMonthAndBefore] <= max(StockInputOutput[YearMonthAndBefore])))
Thank you very much in advance!
Your data model is missing a Calendar/Dates table. If you add that then you can improve your measures to gracefully handle situations where there is no data for a particular material and month.
Hi @lbendlin,
sorry for the late response. Yes, you are right, meanwhile I created a dynamic calnedar table based on the min/max year/month values of StockInputOutput (I added a date field to this table whic is the first day of the month e.g 202107 -> 2021.07.01) and set the connection betwwen the two tables based on dates. So it was missing to show running in the matrix continously.
Thank you for your feedback!
Check out the July 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
7 | |
7 | |
6 | |
6 |
User | Count |
---|---|
27 | |
12 | |
10 | |
9 | |
6 |