cancel
Showing results for 
Search instead for 
Did you mean: 
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-polly-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.


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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors