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:    Month Value January 100 February 250 ...
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) 

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.

StephenMEMC's avatar
StephenMEMC
Advocate I
5 years ago

I figured the EARLIER function referred to the previous record, but I hadn't included an INDEX column of any kind. 

 

I added one, but the further I get into this I think I'm realizing I need to use variables. The data I'm working with spans multiple years, so I need to filter on that as well. Maybe I should go back to the SQL and do the calculation there? That's what I've always done (and then just let Power BI display the result). I'm very new to Power BI so definitely appreciate your explanations and your help.

  • StephenMEMC's avatar
    StephenMEMC
    Advocate I
    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)