Identify next date in a row

[MartinC]

Hi All!

I've done a search and couldn't find an answer to what I'm looking for.  Using the table below, I need to identify which activity is next (i.e. after Today()).  For example using todays date, the first one will be "Tech & Prog Review", the second one will be "Commercial Review" etc.  I'm hoping there's a way to get this info in Power BI?

All help will be appreciated!

Martin

[Anonymous · Accepted solution]

Hi @MartinC ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) In order not to affect the original datasheet, we can copy a datasheet and then select all the columns and right-click on the unpivot column.

(3) We can create a measure and then the result is as follows.

Measure = 
var _a=MINX(FILTER(ALL('Table (2)'),[Value]>TODAY()),[Value])
RETURN CALCULATE(MAX('Table (2)'[Attribute]),FILTER(ALL('Table (2)'),[Value]=_a))

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[MartinC]

I also have new columns that are either 1 or 0 based on the date in the columns above being before (1) today, or after today (0).  The new columns are the correct order, so what I'd be looking for is the first one with a 0.

[IoannisPhilip]

Hi @MartinC ,
maybe this is helpful?
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-get-value-of-the-next-row-for...

[MartinC]

Thanks @IoannisPhilip but I think mine is more complex that those - they are focused on one column, I need the earliest one (name of the column) that is after Today()