Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
85 | |
84 | |
67 | |
63 | |
62 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
71 |