cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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

1 ACCEPTED SOLUTION
Super User

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``````

4 REPLIES 4
Helper III

works like a dream, thanks!

Super User

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
)
)``````

Proud to be a Super User!

Helper III

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

Super User

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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.