Forum Discussion

StephenMEMC's avatar
StephenMEMC
Advocate I
5 years ago
Solved

Data Table Has Running Total Values - Calculate Single Value?

I hope that makes sense what I'm trying to do. I currently have a table that has values in it but they are already aggregated. So for example: 

 

MonthValue
January100
February250
March450

 

The "Value" column is a running total (so in order to get February's single value, you'd subject January and get 150, to get March's value, subject February's to get 200, etc.). Can Power BI do this in some way? I've got a line chart that maps out values over month but of course it just points up. I'd like to have it plot the individual value if possible. I feel sure Power BI can do it I just don't know how.

Need Help
  • StephenMEMC's avatar
    StephenMEMC
    5 years ago

    For the record, I just went with SQL. It's easier for me there. The code I used is as follows:

     

    CAST(Value - isnull((select top 1 Value
                     from TableView t2
                     where t2.YearMonth < t.YearMonth AND LEFT(t2.YearMonth,4) LIKE LEFT(t.YearMonth, 4) + '%' AND t.Value<>0
                     order by YearMonth desc
                    ), 0) AS decimal(10,2)) AS [MonthValue]

     

    The table has a YYYYMM value in YearMonth and an Index (that I think I can get rid of now) 

5 Replies

    • StephenMEMC's avatar
      StephenMEMC
      Advocate I
      5 years ago

      Interesting. So I tried this and it's throwing an error:

       

      MonthValue = Table[Value] - EARLIER(Table[Value])

       

      The error is "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

       

      Sounds like an array out of bounds issue. Is there a way to modify that to not look back when on the first row? 

      • HotChilli's avatar
        HotChilli
        Community Champion
        5 years ago

        EARLIER doesn't refer to an earlier date (always sounds like it should!) but the current value of the column in an "outer evaluation pass of the mentioned column"

        So you might use it in your data to find the current month of the current row->then pass through the data again to find the value associated with the previous month. (phew!)

        In your data and formula, the major thing is:  Powerbi only sees the monthnames here as text fields - it has no idea that "January" is 1 month less than "February". There are lots of ways to enable this (alter the month to be a valid date with date type e.g. 1/month/2020 OR add a new column with a big Switch statement to allocated a number to each month OR if there's one record for each month and the records are in order, just add an index from 1 in Power Query). There are more ways but that'll do.

         

        So if you can get the data to look like

        Month   Value   Index

        January  100      1  

        February 250     2

        etc

         

        you'll be on your way.

        The next stage would be to add a column and try and get the Value from the previous row (we now use Index val to get that) in that column.

        Clue : it'll need an aggregation and you'll want to subtract 1 from the Index to find the row.  Also, the use of variables in DAX can be helpful if EARLIER is confusing

         

        Let me know how you get on.

         

        Apologies if you know all this stuff already.