Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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..
Solved! Go to 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.
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])
)
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.
Thanks, maybe s little too much 'hassle', but I'll look into it!
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! 🙂
@Le-menace , Try
https://docs.microsoft.com/en-us/dax/lastnonblankvalue-function-dax
Something like
lastnonblankvalue(Table[Date],Max(Table[Value]))
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..
Not sure I understand but maybe: https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
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.
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.)
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..