Running sum with Period

[Osmandfernanobi]

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

[Anonymous]

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

[Osmandfernanobi]

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

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

[Osmandfernanobi]

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

Osmand

[Anonymous]

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

Best
D

[Anonymous]

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

[osmandfernando]

Thank you for your support

[ibarrau]

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,

[osmandfernando]

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

[ibarrau]

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,

[osmandfernando]

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

[ibarrau]

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,