Date Fill Down with DAX and Calculated Column

[Anonymous]

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]

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.

[Greg_Deckler · Accepted solution]

@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

[Anonymous]

It worked! Thank you very much!

[Greg_Deckler]

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

[Anonymous]

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!