cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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 I

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 I

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors