topic Re: Running sum with Period in DAX Commands and Tips

Running sum with Period

Osmandfernanobi — Tue, 07 Apr 2020 10:53:30 GMT

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

Re: Running sum with Period

ibarrau — Tue, 07 Apr 2020 13:34:14 GMT

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,

Re: Running sum with Period

osmandfernando — Tue, 07 Apr 2020 12:43:52 GMT

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

ibarrau

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

Re: Running sum with Period

ibarrau — Tue, 07 Apr 2020 12:56:55 GMT

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,

Re: Running sum with Period

osmandfernando — Tue, 07 Apr 2020 13:07:04 GMT

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])))

Re: Running sum with Period

ibarrau — Tue, 07 Apr 2020 13:37:56 GMT

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,

Re: Running sum with Period

Anonymous — Sun, 12 Apr 2020 14:02:39 GMT

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

Re: Running sum with Period

Osmandfernanobi — Mon, 13 Apr 2020 04:23:57 GMT

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

Re: Running sum with Period

Anonymous — Mon, 13 Apr 2020 09:55:42 GMT

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

Re: Running sum with Period

Osmandfernanobi — Mon, 13 Apr 2020 14:13:40 GMT

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

Osmand

Re: Running sum with Period

Anonymous — Mon, 13 Apr 2020 14:34:27 GMT

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

Re: Running sum with Period

Anonymous — Mon, 13 Apr 2020 14:36:30 GMT

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

Best
D

Re: Running sum with Period

osmandfernando — Tue, 14 Apr 2020 05:58:35 GMT

Thank you for your support