Forum Discussion
Running total calculation without filter?
OK I think I found it, add "<" to this measure
Looking good!
Hey aj1973 ,
thank you for your fast reply! I tried it, but the running total calculation does not work on this way (as it can be seen on the picture you shared)
m_StockIORunningTotal1 =
var StockIODiff = [m_StockIODiffForRunningTotal]
var RT = CALCULATE(
StockIODiff,
FILTER(
ALLSELECTED('Calendar'[0BeforeActualYearMonth]),
'Calendar'[0BeforeActualYearMonth] >= [m_ActualMonth] && 'Calendar'[0BeforeActualYearMonth] <= max('Calendar'[0BeforeActualYearMonth]))
)
return RT
Your exact measure is working for me
here is the file
https://drive.google.com/file/d/1a1tcHD3KlIr6UBf32c5BizA1qYttDMCO/view?usp=sharing
I downloaded the file and checked again but unfortunately your solution (in m_StockIORunningTotal) provides same result as the m_StockIODiffForRunningTotal which counts the start balance for running total. The 3rd visualization shows the running total correctly but the 0BeforeActual column is missing:
Please see the details in this RunningTotalDemoWithCalendar1_aj1973
file.
I didn't touch nor verify your calculations/measures. I just answered your request which is you wanted to see OBeforeActual coloumn in the Matrix.
Hey,
If i use the logic you used in your measure i get this
Is it correct!
the difference resides in your measure when you use [m_ActualMonth]
where is that measure coming from? can't find it anywhere!
it is like the measure is coming from a different table, which is not correct inside the FILTER.
Hey aj1973,
firstly, I really appreciate your help and thank you for your efforts for solving this issue!
Sorry, If I was not clear enough, so I try to summarize the details in the following:
1. as I wrote in the first post, the running total calculation base is a start balance which contains:
the actual stock quantity, stock input output quantity before the actual month + actual month quantity. This start balance is the value of the actual month (actually it is 202107), in case of other months simply the StockInputOutput[IODiff] value is calculated.
This is provided by The m_StockIODiffForRunningTotal2. The m_actualMonth measure is for providing the actual month value and it is int the LoadedAt table which was hidden in report view, now I set it visible.
"it is like the measure is coming from a different table, which is not correct inside the FILTER."
If I understood correctly, if the measure in the Calendar table then it is ok, so I created the
m_CalendarActualMonth = calculate(min('Calendar'[0BeforeActualYearMonth]),'Calendar'[0BeforeActualYearMonth] <> "0BeforeActual")measure in Calandar table and used it instead of m_actualMonth in the other measures.
Unfortunately, it did not solve the problem, the running total is ok, but the "0BeforeActual" column is hidden in visual. You can check it in this RunningTotalDemoWithCalendar1_aj1973_2 file.
3. Running total
This running total calculation shows the "0BeforeActual" column but it is nok, becasuse does not calculate with the start balance:
I hope, it helps to understand my aspect. Thank you!