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.
Hi team,
I need help with a dax calculated column. I have a datetime column and value column. I need a dax calulation to take the previous nonblank value. Here is the needed result (the right column):
DateTime mm/dd/yyyy hh:mm:ss | Value | Needed Result |
6/14/2022 09:00:00 PM | 100 | 100 |
6/14/2022 10:00:00 PM | 107 | 107 |
6/14/2022 11:00:00 PM | 107 | |
6/15/2022 00:00:00 AM | 107 | |
6/15/2022 01:00:00 AM | 107 | |
6/15/2022 01:00:00 AM | 200 | 200 |
6/15/2022 02:00:00 AM | 200 | |
6/15/2022 03:00:00 AM | 300 | 300 |
When there is empty rows, the calculation should take the previous nonblank row value and copy it, until there is data in the value column.
Thank you.
Solved! Go to Solution.
Hi @EmaVasileva,
DAX to do that.
Column =
VAR a =
MAXX (
TOPN (
1,
FILTER (
'Table',
[DateTime mm/dd/yyyy hh:mm:ss]
< EARLIER ( 'Table'[DateTime mm/dd/yyyy hh:mm:ss] )
&& NOT ( ISBLANK ( [Value] ) )
),
[DateTime mm/dd/yyyy hh:mm:ss], DESC
),
[Value]
)
RETURN
IF ( [Value] = BLANK (), a, [Value] )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @EmaVasileva,
DAX to do that.
Column =
VAR a =
MAXX (
TOPN (
1,
FILTER (
'Table',
[DateTime mm/dd/yyyy hh:mm:ss]
< EARLIER ( 'Table'[DateTime mm/dd/yyyy hh:mm:ss] )
&& NOT ( ISBLANK ( [Value] ) )
),
[DateTime mm/dd/yyyy hh:mm:ss], DESC
),
[Value]
)
RETURN
IF ( [Value] = BLANK (), a, [Value] )
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@EmaVasileva just to be sure, you don't want to do it in Power Query? Just 2 steps
User | Count |
---|---|
86 | |
82 | |
68 | |
64 | |
55 |
User | Count |
---|---|
120 | |
99 | |
91 | |
83 | |
65 |