How to load the lastest non-blank figure?

[My_Rebecca]

How to load the lastest non-blank figure by expression?

eg:

if today() is 3/27/2023, the result is 3650;

if today() is 4/3/2023, the result is 1660;

if today() is 4/6/2023, the result is 4685；

if today() is 4/10/2023, the result is 6685.

[sandeep-singh]

Try this

Dummy:
load * inline [
Week,Date,Total DHL DN line
2023-WK12,3/21/2023,8707
2023-WK12,3/22/2023,
2023-WK12,3/23/2023,5650
2023-WK12,3/24/2023,
];

Load
Week,
Date,
if(Len(Trim([Total DHL DN line]))=0, peek('Total DHL DN line_1'),[Total DHL DN line]) as [Total DHL DN line_1]
Resident Dummy;

drop table Dummy;

[My_Rebecca]

@sandeep-singh 

dear, there are many lines in this sheet and I needn't the script, but expression while editing the sheet in app, so could you please support to have another look at this question? thanks.

[sandeep-singh]

Try this

You can use the below script in the measure

=if(len(trim([Total DHL DN line]))=0
,above([Total DHL DN line])
,[Total DHL DN line])

[My_Rebecca]

@sandeep-singh 

dear,

what about "if today() is 4/10/2023, the result is 6685"? The result is not above line, but above 2 lines. It should be nearest non-blank, not above.

[My_Rebecca]

Is there anyone who can support me on this expression? thanks.

[sandeep-singh]

There is no such alternative of "peek" function in the front end. The best possible that you can do is add a piece of script mentioned in the previous conversation or you can add it with preceding load like below, but do verify the table count as I am not aware of your data model

load *,
if(Len(Trim([Total DHL DN line]))=0, peek('Total DHL DN line_1'),[Total DHL DN line]) as [Total DHL DN line_1]
;

[My_Rebecca]

@sandeep-singh 

dear, please look at my data model. It contains hundreds of rows here.

could you please support to edit the script here? Many thanks.

[sandeep-singh]

You just have to add this piece of script as a preceding load where you are loading these fields from data source. Adjust the field name accordingly.

load *,
if(Len(Trim([Total DHL DN line]))=0, peek('Total DHL DN line_1'),[Total DHL DN line]) as [Total DHL DN line_1]
;

[My_Rebecca]

Dear @sandeep-singh , 'Total DHL DN line_1' does not exist, so I think the preceding loading would be infeasible. Is there any other way to solve this issue? thanks.