Forum Discussion

Osmandfernanobi's avatar
Osmandfernanobi
Regular Visitor
6 years ago

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
DAX
running total

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Osmandfernanobi
      Regular Visitor
      6 years ago

      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

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    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's avatar
      osmandfernando
      Advocate I
      6 years ago

      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's avatar
        ibarrau
        Super User
        6 years ago

        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,