Get value from 'previous' row that was calculated by measure

[m4xon]

Hello,

I have following problem, I need to calculate a specific value, but to do that each time I have to use as a part of calculation a value from the prior row (based on the q-yyyy). I have in a table column from source table [Quarters] and I added one measure to count total rows

Total = CALCULATE ( COUNTROWS ( dimSource ), dimSource[Action] = "OK" )

and now I need a measure that will calculate something extra but using the "total" from previous measure and previous row 🙂

Indx = 
VAR _a =
    CALCULATE (
        MAX ( 'dimSource'[Quarter] ),
        FILTER (
            ALL('dimSource'), 
            [Quarter] < MAX ( 'dimSource'[Quarter] )
        )
    )

VAR _result =
CALCULATE (
        MAX ( 'dimSource'[Quarter] ),
        FILTER (
            ALL ('dimSource'),
            [Quarter] = _a
        )
    )
RETURN
    _result

Based on the above I get a formula which returns to me what is the preceding quarter, but I need a total value instead of quater 

[m4xon]

works like a dream, thanks!

[Nathaniel_C]

Hi @m4xon ,
If I understand the problem, replace the quarter value with the total value, and replace the = with<=

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

VAR _result =
CALCULATE (
        MAX ( 'dimSource'[Total] ),
        FILTER (
            ALL ('dimSource'),
            [Quarter] < = _a
        )
    )

[m4xon]

Hello,

It would be to easy 😉 We don't have a column [Total], it's a measure. In base table we have only [Quarter], this is the question, how to put a measure into it.

Thanks!

Max

[johnyip · Accepted solution]

@m4xon 

Try this:

Total = 
VAR LastQuarter =
    CALCULATE (
        MAX ( 'dimSource'[Quarter] ),
        FILTER (
            ALL('dimSource'), 
            [Quarter] < MAX ( 'dimSource'[Quarter] )
        )
    )
VAR StagingTable = FILTER(ALLSELECTED(dimSource),[Action]="OK" && [Quarter] = LastQuarter)

VAR _result =  SELECTCOLUMNS(SUMMARIZE(StagingTable,
                                    [Quarter],
                                    "Count",CALCULATE(COUNTROWS (StagingTable))
                            ),
               "Total",MIN([Count]))

RETURN
    _result