Calculate the value base on previous Matrix rows

[TinaL22]

Hello Everyone,

I am currently struggling to calculate a DAX value base on previous matrix row. Here is an example shows what I am tring to do.

Imagine this is the matrix I created in the BI, I am trying to calculate column D use DAX, 166 is a fix number for 2020-04-01, but everything below that is base on previous row. Like D2 for 2020-05-01 row is D1+B1+C1, and D3 for 2020-06-01 is base on calculated D2 number plus B2 and C2. column B and C also calculated by DAX.Could anyone help me on this?

I really preciate your help!

[edhans · Accepted solution]

You should really consider restructuring your data @TinaL22 and when you see the answer here is why. this is the measure that works - add this to a Matrix visual.

New Total = 
VAR varCurrentDate = MAX(Data[Date])
VAR varMinDate = 
    CALCULATE(
        MIN(Data[Date]),
        REMOVEFILTERS(Data[Date])
    )
VAR Result = 
    CALCULATE(
        SUM(Data[Column1]) + SUM(Data[Column2]) + SUM(Data[Column3]),
        FILTER(
            ALL(Data),
            Data[Date] <= varCurrentDate
        )
    ) - 
    CALCULATE(
        SUM(Data[Column1]) + SUM(Data[Column2]),
        FILTER(
            ALL(Data),
            Data[Date] = varMinDate
        )
    )
RETURN
    IF(
        MAX(Data[Date]) = varMinDate,
        MAX(Data[Column3]),
        Result
    )

It returns this:

Which is the answer you want, but there are some issues.

1. You cannot add data to column3 in this example. In Power BI, either everything is a formula (calculated column or measure) or none of it is. You cannot have the first row be a value then rows 2-n be calculations on that.
2. I got around that by adding a "New Total" so you would probably remove column 3 from your matrix visual. Still, now row 1 calc is just 166 or column 3, but row 2 is the cumulative of column 2 plus the cumulatives of columns 1 & 2, except for row 1. So that has to be backed out.

So while this works, it isn't ideal and isn't terribly flexible. If you can figure out how to restrcutre your data into a more normalized table where the logic flows that whatever you put in the "New Total" value is consistent formula without special handling for just the first row of the data.

[edhans · Accepted solution]

You should really consider restructuring your data @TinaL22 and when you see the answer here is why. this is the measure that works - add this to a Matrix visual.

New Total = 
VAR varCurrentDate = MAX(Data[Date])
VAR varMinDate = 
    CALCULATE(
        MIN(Data[Date]),
        REMOVEFILTERS(Data[Date])
    )
VAR Result = 
    CALCULATE(
        SUM(Data[Column1]) + SUM(Data[Column2]) + SUM(Data[Column3]),
        FILTER(
            ALL(Data),
            Data[Date] <= varCurrentDate
        )
    ) - 
    CALCULATE(
        SUM(Data[Column1]) + SUM(Data[Column2]),
        FILTER(
            ALL(Data),
            Data[Date] = varMinDate
        )
    )
RETURN
    IF(
        MAX(Data[Date]) = varMinDate,
        MAX(Data[Column3]),
        Result
    )

It returns this:

Which is the answer you want, but there are some issues.

1. You cannot add data to column3 in this example. In Power BI, either everything is a formula (calculated column or measure) or none of it is. You cannot have the first row be a value then rows 2-n be calculations on that.
2. I got around that by adding a "New Total" so you would probably remove column 3 from your matrix visual. Still, now row 1 calc is just 166 or column 3, but row 2 is the cumulative of column 2 plus the cumulatives of columns 1 & 2, except for row 1. So that has to be backed out.

So while this works, it isn't ideal and isn't terribly flexible. If you can figure out how to restrcutre your data into a more normalized table where the logic flows that whatever you put in the "New Total" value is consistent formula without special handling for just the first row of the data.

[TinaL22]

Thank you so much, this is really helpful.

[edhans]

Glad I was able to assist @TinaL22 !

[TinaL22]

Hi @edhans, If you don't mind I ask you one additional question on this. So right now based on your measure I calculated the last column, which is great. But right now I want to change column A from date format to text. For example, 2020-04-01 shows Apr-20, 2020-05-01 shows May-20. After the changes the measure stope working, which I understand in the measure it compares the date, not the text. So I wondering if you have any idea how to make it work. Thank you so much.

[edhans · Accepted solution]

Can you just change the look of the date?

Additionally, you an set custom formats per this article.

You cannot do calculations based on text data. If you must have a text field, then you need to add a new column in Power Query that converts the date to the Text format you want and show that in the visual. You could then remove the actual date. The measure would still work. But I don't recommend that process unless absolutely necessary. Changing the format is the best way to go here IMHO.

[TinaL22]

Then I feel I just change the look, that will be the easiest solution, thank you so much👍

[edhans]

Glad to help @TinaL22