Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Le-menace
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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
v-lionel-msft
Community Support
Community Support

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.

@v-lionel-msft 

Thanks, maybe s little too much 'hassle', but I'll look into it!

Anonymous
Not applicable

 

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

=MAX(ARRAYFORMULA(A2:A))

See example file I've prepared: find maximum date

@Anonymous 

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

amitchandak
Super User
Super User

@Le-menace , Try

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

Something like

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

 

 

@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..

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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.

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi again @Greg_Deckler 

This of course worked. Thanks a lot! (due to a filter that I didn't know was still on, I just could't see.. Sorry, a little embarrassing.)

Glad you got it @Le-menace !

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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..

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.