cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Last date when last date differs among the columns

Hi

I have a table where the last input in the diffrent columns normally differ regarding the corresponding date. How can I create a measure that give me the date for the last input in the columns? Of course, column B is easy, but what about the rest?

I'm sure it should be rather easy as well, and I'll be grateful if somone please could please enlighten me..

 

example.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Last date when last date differs among the columns

Oh, well then that's no problem:

 

Last Non Blank of C Column = MAXX(FILTER('Table',NOT(ISBLANK([C])),[Date])

 

You should be able to figure out the other columns from that one, just replace C with D, E, F, etc.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

12 REPLIES 12
Highlighted
Super User IX
Super User IX

Re: Last date when last date differs among the columns

Not sure I understand but maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: Last date when last date differs among the columns

@Le-menace , Try

https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax

Something like

lastnonblankvalue(Table[Date],Max(Table[Value]))

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Last date when last date differs among the columns

@amitchandak

Thanks a lot for quick reply! This solution unfortunately only seems to give me the (last) date of the last row with no blanks (that is 5/5/2020). For column C/D i want 7/5/2020 as output and for column E 8/5/2020 etc..

Highlighted
Helper I
Helper I

Re: Last date when last date differs among the columns

@Greg_Deckler 

Unfortunatly I'm not sure I understood your reply either, but thanks anyway. 🙂

I can try to clarify. I want the dates of the last input values in the different columns. For column B the solution is 11/5/2020. For column C and D the solution is 7/5/2020, for column E-G 8/5/2020 and for column H 5/5/2020.

Highlighted
Super User IX
Super User IX

Re: Last date when last date differs among the columns

Oh, well then that's no problem:

 

Last Non Blank of C Column = MAXX(FILTER('Table',NOT(ISBLANK([C])),[Date])

 

You should be able to figure out the other columns from that one, just replace C with D, E, F, etc.


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Post Partisan
Post Partisan

Re: Last date when last date differs among the columns

 

The following formula will find the maximum date in column A:

=MAX(ARRAYFORMULA(A2:A))

See example file I've prepared: find maximum date

Highlighted
Helper I
Helper I

Re: Last date when last date differs among the columns

@Greg_Deckler 

Thanks again! I might (probably so) be doing something wrong, but this still seems to give me the date of the last row with no blanks (5/5/2020). It doesn't matter which column I specify after "isblank". Why? I don't know..

Highlighted
Helper I
Helper I

Re: Last date when last date differs among the columns

@Lewis-H 

Hm, where can I see this example of yours? Thanks anyway! 🙂

Highlighted
Community Support
Community Support

Re: Last date when last date differs among the columns

Hi @Le-menace ,

 

Try to create a measure like this:

_Date = 
VAR x2 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column2] ) )
VAR x3 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column3] ) )
VAR x4 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column4] ) )
VAR x5 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column5] ) )
VAR x6 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column6] ) )
VAR x7 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column7] ) )
VAR x8 = LASTNONBLANKVALUE( ALL('Table'[Column1]), MAX('Table'[Column8] ) )
RETURN
SWITCH(
    TRUE(),
    SELECTEDVALUE('Table'[Column2]) = x2, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column3]) = x3, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column4]) = x4, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column5]) = x5, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column6]) = x6, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column7]) = x7, SELECTEDVALUE('Table'[Column1]),
    SELECTEDVALUE('Table'[Column8]) = x8, SELECTEDVALUE('Table'[Column1])
)

k3.PNG

 

Best regards,
Lionel Chen

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

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors