topic Re: How to get value of the next row for a certain category? in DAX Commands and Tips

How to get value of the next row for a certain category?

justlogmein — Sun, 05 Jun 2022 07:50:56 GMT

I have been able to create a calculated column using the EARLIER function, however I am unsure on how to find the next row (or more than 1 row earlier for that matter).

The DAX below is what I have used to find the previous Sample Date for the same unit number. Can someone please tell me how I would find the next sample date and if there is a better way of writing my current formula? I feel like using MAX or SUM here is a little dodgy as I am not actually finding either, I just want to return a single value.

= CALCULATE ( MAX ( [Sample Date] ), FILTER ( 'Sample Data', 'Sample Data'[Sample Date] < EARLIER ( 'Sample Data'[Sample Date] ) ), 'Sample Data'[Unit Number] = EARLIER ( 'Sample Data'[Unit Number] ) )

Re: How to get value of the next row for a certain category?

AlB — Sun, 05 Jun 2022 08:18:33 GMT

Hi justlogmein

NewCol = VAR previousDate_ = CALCULATE ( MAX ( [Sample Date] ), 'Sample Data'[Sample Date] < EARLIER ( 'Sample Data'[Sample Date] ), ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] ) ) RETURN CALCULATE ( MAX ( [Sample Date] ), 'Sample Data'[Sample Date] < previousDate_, ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] ) )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to get value of the next row for a certain category?

AlB — Sun, 05 Jun 2022 08:26:48 GMT

Or if you want more generic solution, , where you can specify the poisition you want by changing N_ (4th last in the example):

A = VAR N_ = 4 // 1 is the immediately previous, 2 is the one before and so forth VAR auxT_ = TOPN ( N_, CALCULATETABLE ( DISTINCT ( 'Sample Data'[Sample Date] ), 'Sample Data'[Sample Date] < EARLIER ( 'Sample Data'[Sample Date] ), ALLEXCEPT ( 'Sample Data', 'Sample Data'[Unit Number] ) ), 'Sample Data'[Sample Date], DESC ) RETURN MINX ( auxT, 'Sample Data'[Sample Date] )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: How to get value of the next row for a certain category?

tamerj1 — Sun, 05 Jun 2022 09:18:50 GMT

Hi justlogmein

Please use

Top 1 Unit Number = VAR CategoryTable = CALCULATETABLE ( 'Sample Data', ALLEXCEPT ( 'Sample Data', 'Sample Data'[Category] ) ) VAR Top1Value = MAXX ( CategoryTable, 'Sample Data'[Sample Date] ) VAR Top1UnitNumber = MAXX ( FILTER ( Top2Table, 'Sample Data'[Sample Date] = Top1Value ), 'Sample Data'[Unit Number] ) RETURN Top1UnitNumber

2nd Top Unit Number = VAR CategoryTable = CALCULATETABLE ( 'Sample Data', ALLEXCEPT ( 'Sample Data', 'Sample Data'[Category] ) ) VAR Top2Table = TOPN ( 2, CategoryTable, 'Sample Data'[Sample Date] ) VAR Top2Value = MINX ( Top2Table, 'Sample Data'[Sample Date] ) VAR Top2UnitNumber = MAXX ( FILTER ( Top2Table, 'Sample Data'[Sample Date] = Top2Value ), 'Sample Data'[Unit Number] ) RETURN Top2UnitNumber

Re: How to get value of the next row for a certain category?

Jihwan_Kim — Sun, 05 Jun 2022 09:35:04 GMT

Hi,

Please try the below for creating a new column.

next sample date CC = MINX ( FILTER ( 'Sample Data', 'Sample Data'[Sample Date] > EARLIER ( 'Sample Data'[Sample Date] ) && 'Sample Data'[Unit Number] = EARLIER ( 'Sample Data'[Unit Number] ) ), 'Sample Data'[Sample Date] )

Re: How to get value of the next row for a certain category?

Anonymous — Wed, 08 Jun 2022 02:00:14 GMT

Hi justlogmein ,

Please modify your formula.

Column = CALCULATE ( MIN ( 'Sample Date'[Sample Date] ), FILTER ( 'Sample Date', 'Sample Date'[Sample Date] > EARLIER ( 'Sample Date'[Sample Date] ) && 'Sample Date'[unit number] = EARLIER ( 'Sample Date'[unit number] ) ) )

Or try to use Jihwan_Kim 's way.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.