Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
EnderWiggin
Helper I
Helper I

Running total calculation without filter?

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

EnderWiggin_0-1626354601887.png

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!

1 ACCEPTED SOLUTION

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

aj1973_0-1626437542675.png

 

Looking good!

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

13 REPLIES 13
aj1973
Community Champion
Community Champion

Hi @EnderWiggin 

here you go

aj1973_0-1626356882942.png

Use VAR to nest your measure into CALCULATE

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

aj1973_0-1626358057792.png

here is the file

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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:

EnderWiggin_1-1626364827616.png

Please see the details in this RunningTotalDemoWithCalendar1_aj1973

file.

Hey,

If i use the logic you used in your measure i get this

aj1973_0-1626369442911.png

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!

aj1973_1-1626369636548.png

it is like the measure is coming from a different table, which is not correct inside the FILTER.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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:

EnderWiggin_1-1626384734068.png

I hope, it helps to understand my aspect. Thank you!

 

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 --->
aj1973_0-1626404488814.png

 

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

  

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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 

EnderWiggin_0-1626417006700.png

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!

 

 

 

IF condition.

aj1973_1-1626438069145.png

 

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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

aj1973_0-1626437542675.png

 

Looking good!

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Hey aj1973,

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

EnderWiggin_0-1626438712322.png

 

Have nice day!

Indeed,

 

Welcome

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.