Skip to main content
cancel
Showing results for 
Search instead 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

Reply
justlogmein
Helper III
Helper III

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

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

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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

SU18_powerbi_badge

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.

 

View solution in original post

5 REPLIES 5
v-rongtiep-msft
Community Support
Community Support

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

vpollymsft_0-1654653493102.png

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.

Jihwan_Kim
Super User
Super User

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here and Go to My LinkedIn Page


tamerj1
Super User
Super User

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

 

 

AlB
Super User
Super User

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

SU18_powerbi_badge

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.

 

AlB
Super User
Super User

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

 

 

SU18_powerbi_badge

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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