Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone!
I am trying to create a fill down with date and time. The example that I am using is the following:
The desired outcome is colored.
I have tried using some of the forums' answers like this link:
https://community.powerbi.com/t5/Desktop/Dax-fill-down-in-formula/m-p/319171#M142022
but it didn't work...
This is the Calculated Column I tried (I also have Date and Hour columns used to create the Datetime column):
VAR LastNonBlankDate =
CALCULATE (
LASTNONBLANK ( 'Table'[Date], 1 ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] <= EARLIER ( 'Table'[Date] )
&& NOT ( ISBLANK ( 'Table'[First Datetime] ) )
)
)
RETURN
CALCULATE (
SUM ( 'Table'[First Datetime] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = LastNonBlankDate )
)
What am I doing wrong?
For what I need it has to be a calculated column, so I can't do it on Power Query.
Here is a Sample Data:
https://docs.google.com/spreadsheets/d/1-Wd4xz2lkjrr7NvP-8rDrUW2725gakAF6pBcF8qgIKk/edit
Thank you
Solved! Go to Solution.
@Ana_123 Try this:
Last Date Column =
VAR __DateTime = [Datetime]
VAR __Tag = [Tag]
VAR __Table = FILTER('Table',[First Datetime] <> BLANK() && [Tag] = __Tag)
VAR __LastDateTime = MAXX(FILTER(__Table,[Datetime] <= __Datetime),[Datetime])
RETURN
__LastDateTime
Update:
I was able to do it with this calculated column:
Last Date =
VAR LastNonBlankDate =
CALCULATE (
LASTNONBLANK ( 'Table'[Datetime],1 ),
FILTER ('Table',
'Table'[Datetime] <= EARLIER ( 'Table'[Datetime] )
&& NOT ( ISBLANK ( 'Table'[first] ) )
&& 'Table'[Tag]=EARLIER('Table'[Tag])
)
)
RETURN LastNonBlankDate
But when I tried using it with a bigger base I got an error message "Not Enough Memory To Complete This Operation"...so if there are any other suggestions...
Thank you again.
@Ana_123 Try this:
Last Date Column =
VAR __DateTime = [Datetime]
VAR __Tag = [Tag]
VAR __Table = FILTER('Table',[First Datetime] <> BLANK() && [Tag] = __Tag)
VAR __LastDateTime = MAXX(FILTER(__Table,[Datetime] <= __Datetime),[Datetime])
RETURN
__LastDateTime
It worked! Thank you very much!
@Ana_123 You'll have to add an Index column. That can be done in DAX if necessary.
(1) The Mythical DAX Index - Microsoft Power BI Community
Hi Greg_Decker, thank you for the idea!
I think I can add index on Power Query, but I will think on a way to use the index to fill down those dates first.
Update:
I changed my calculated column to:
Last Date =
VAR LastNonBlankDate =
CALCULATE (
LASTNONBLANK ( 'Table'[Datetime],1 ),
FILTER (
ALL ( 'Table' ),
'Table'[Datetime] <= EARLIER ( 'Table'[Datetime] )
&& NOT ( ISBLANK ( 'Table'[first] ) )
)
)
RETURN LastNonBlankDate
while the "first" column is:
it worked for some of the Datetimes, but not all...if anyone has any more ideas on how to change it...
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
95 | |
82 | |
71 | |
64 |
User | Count |
---|---|
115 | |
105 | |
95 | |
79 | |
72 |