Running total calculation without filter?

[EnderWiggin]

Hi All,

I created a running total calculation which counts from a start balance. The start balance contains the actual stock quantity, stock input output quantity before the actual month + actual month quantity.

Please, see this PBI file link for more details: RunningTotalWithCalendar 

My problem is that  the "0BeforeActual" column is not visible in visualization because of the applied filter, as you can see on the picture below. 

The first visualization shows the start balance calculation and the second one shows the running total

Running total measure

m_StockIORunningTotal = 
CALCULATE(
	[m_StockIODiffForRunningTotal],
	FILTER(
		ALLSELECTED('Calendar'[0BeforeActualYearMonth]),
		'Calendar'[0BeforeActualYearMonth] >= [m_ActualMonth] && 'Calendar'[0BeforeActualYearMonth] <= max('Calendar'[0BeforeActualYearMonth]))
	)

 Is there any way to create running total calculation without filtering the "0BeforeActual"  column? 

Thank you in advance!

[aj1973]

Hi @EnderWiggin 

here you go

Use VAR to nest your measure into CALCULATE

[EnderWiggin]

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

[aj1973]

Your exact measure is working for me

here is the file

https://drive.google.com/file/d/1a1tcHD3KlIr6UBf32c5BizA1qYttDMCO/view?usp=sharing

[EnderWiggin]

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.

[aj1973]

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.

[EnderWiggin]

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_StockIODiffForRunningTotal 

2. 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!

[aj1973]

Hey,

I have been trying hard to understand the logic of your measure but in vain, however I can say that this 'Calendar'[0BeforeActualYearMonth] is of type Text and numbers therefore not possible to compare it to a scalar of type number, I am refering to 

MAX('Calendar'[0BeforeActualYearMonth]), same thing for the other measure.

I Replaced && by | | to see the difference and --->

Conclusion : I am not sure the filter is working properly and I wonder if the result is thus correct! 

  

[EnderWiggin]

Hey aj1973,

Ok,  I changed the type of the 0BeforeActualYearMonth from text to number and modified the related measures according to the changes. Here is the result: RunningTotalDemoWithCalendar1_aj1973_3 

I don't see any significant changes in the result. If the running total with start balance works then the 0 column (this was 0BeforeActual value in the previous versions) is not visible. If the 0 column is visible then the running total does not work.

So, back to my original question:

"Is there any way to create running total calculation without filtering the "0BeforeActual"  column?" 

Thank you!

[aj1973]

IF condition.

https://drive.google.com/file/d/1sxZeTXPSJIuIepzG2v2ckvBsnbW_Pe9u/view?usp=sharing

This is how I found out that your measure is missing the "<"

Hope your dilema is over.

[aj1973 · Accepted solution]

OK I think I found it, add "<" to this measure

Looking good!

[EnderWiggin]

Hey aj1973,

yes, you did it!! Thank you for your solution and your efforts and patiance during this loong conversation 

Have nice day!

[aj1973]

Indeed,

Welcome

[aj1973]

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.