Forum Discussion
Date Fill Down with DAX and Calculated Column
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
Anonymous 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
5 Replies
- Greg_Deckler
Community Champion
Anonymous You'll have to add an Index column. That can be done in DAX if necessary.
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 LastNonBlankDatewhile 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!
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 LastNonBlankDateBut 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.
- Greg_Deckler
Anonymous 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