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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Running sum with Period

Dear experts,

I have a situation where I wanted to show the running total in a line chart for Actuals, Expected, and Variance, we are able to get the values and create the visual. But having a small issue on the actual and variance line. The budget is for the 12 periods, but actual is only for 6 months. Therefore we wanted to stop the actual and variance line where we have actual expenses (period 6) but it shows until period 12. I have attached here a screenshot for your easy reference.

Following is the formula has been set up,

Actual = CALCULATE(SUM(Burn[ACTUAL]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
Variance = CALCULATE(SUM(Burn[VARIANCE]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
Expected = CALCULATE(SUM(Burn[BUDGET]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))

How can we stop the actual and variance lines from the current actual period?

Osmand
12 REPLIES 12
Anonymous
Not applicable

I can see you don't have a proper Date table in the model. This will lead to a lot of issues and bugs you will not even be aware of. Please watch these before starting modeling in PBI:

https://www.youtube.com/watch?v=78d6mwR8GtA

https://www.youtube.com/watch?v=_quTwyvDfG0

and read this to know why you should always use correct star schemas:

https://www.sqlbi.com/articles/understanding-dax-auto-exist/

Also, here's how to properly deal with the future in time-intel calculations:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

Best
D

Regular Visitor

@Anonymous Thank you for your great ideas.

@ibarrau Thank you for your replies, I tried the formula you gave me but it did not work. To make the things simple I created a simple table visual. Then I created a Running sun measure for actuals. The following is the screenshot of it. I want to stop showing the running total from Period 6 onwards. How can I do it.

Following is the formula I have for the running total

ACTUAL running total in MONTH =
CALCULATE(
SUM('Burn'[ACTUAL]),
FILTER(
ALLSELECTED('Burn'[MONTH]),
ISONORAFTER('Burn'[MONTH], MAX('Burn'[MONTH]), DESC)
)
)

Osmand

Anonymous
Not applicable
Hi there. If you really had taken my advice seriously, you'd now know how to do such a thing:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

Best
D
Regular Visitor

Thank for your article I am trying to apply your solution but I am still unable to get it correct.

Osmand

Anonymous
Not applicable
You will not be getting things right until you have a correct model where there are separate date/time dimensions.

Best
D
Anonymous
Not applicable
That's because you are not following Best Practices. Please have a look at how to correctly apply time intelligence in a model:

https://www.sqlbi.com/tv/time-intelligence-in-microsoft-power-bi/

Best
D
Advocate I

Thank you for your support

Super User

Hi. I see. The idea of this is to recognize the "Stop Here" automatically. We can help you do it if you tell us more about it

Is actual in a separate table?

Is there only one table in the model?

For now I'll show an example without it. You can resolve this in two different ways. The first and most importante is to recognize automatically the "max_date" variable. Then you can just call the calculation before that date or complete with blank for the next days of that date:

``````Measure 1 =
VAR max_date = DATE(2009;1;1)
RETURN
CALCULATE( [SalesAmount]; InternetSales[Order Date] < max_date )

Measure 2 =
VAR max_date = DATE(2009;1;1)
RETURN
IF(
MAX(InternetSales[Order Date]) > max_date ;
BLANK();
[SalesAmount]
)``````

Build this measures for Budget and Variance (my sales amount should be your budget)

Hope this helps,

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Advocate I

Same Osmand here, I am confused with my community accounts,

Actual is not a separate table, All those 3 data fields (Actual, Expected, Variance) are in a new table created based on a another table name Burn,

There are several tables as you can see below

Thank you

Osmand

Super User

Well, those measures are probably the "Sum" of something. Can you show us the columns involved in the tables that are used in the measures? Right there I can't see anything related with actual due to measures are not related with the table in which they are created.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Advocate I

Thank you for the reply

following are the formulas

Actual = CALCULATE(SUM(Burn[ACTUAL]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
Variance = CALCULATE(SUM(Burn[VARIANCE]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
Expected = CALCULATE(SUM(Burn[BUDGET]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
Super User

Try using one of my formulas changing the max_date like this:

``````Measure =
VAR max_date =
CALCULATE (
MAX(Burn[TrxnDate]);
FILTER(
ALL(Burn[TrxnDate];Burn[ACTUAL]);
OR( Burn[ACTUAL]>0 ; Burn[ACTUAL] <> BLANK() )
)
)
RETURN
--Formula--``````

That should be the max date of the actual values. Then you can use that as variable of the other formulas.

Regards,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors