Forum Discussion
Data Table Has Running Total Values - Calculate Single Value?
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)
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.
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)