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
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!
Solved! Go to Solution.
OK I think I found it, add "<" to this measure
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
Hi @EnderWiggin
here you go
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
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:
Please see the details in this RunningTotalDemoWithCalendar1_aj1973
file.
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.
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.
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!
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
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
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.
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
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
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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
102 | |
92 | |
69 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
95 |