Help WIth Moving FIRST NONBLANKROWS from 1 Column to another

[Anonymous]

Greeting my fellow user,

From my table called Data, I currently trying to insert the first nonblank value from START to the same row as the firstnonblank value from FINISH which at the column RESULT. There are thousands of REQUEST NO. with multiple START and FINISH date inside each REQUEST NO. and also with different amount of space for each first nonblank value of START and FINISH.

I tried using 
RESULT = IF( 'DATA'[FINISH] <> BLANK(),
                 CALCULATE(FIRSTNONBLANK('DATA'[START], 1), ALLEXCEPT('DATA', 'DATA'[RANK])),

                 BLANK() )

But to no avail. Would appreciate every feedback given for this problem. 
 

Thanks for reading my question!

REQUEST NO.	RANK	START	FINISH	RESULT
1938084	1	2/2/2020
1938084	1
1938084	1
1938084	2		3/2/2020	2/2/2020
1938084	2
1938084	3	4/2/2020
1938084	4		10/2/2020	4/2/2020
1938084	4
1938084	4

[az38]

Hi @Anonymous 

Im not sure you are correct either in data example or task

for this row

1938084

2

3/2/2020

there is should be blank value as Rank=2 has no start date in any row

the same about Rank=4

your statement looks good and for me works good, but im not sure about "BLANK". Its better to use BLANK()

[Anonymous]

Hi @az38,

Thanks for the kind response. I'm sorry about the BLANK, I've used BLANK() but written it wrongly in the example. I've edit the example.

About the RANK, ya that's the problem. The RANK for START and FINISH are different thus why my coding resulted in blank rows.

Is is possible to transfer the value of the first non blank row from START to the first non blank row from FINISH eventhough the RANK are different?

Thank you for the time taken to reply my question!

[az38]

@Anonymous 

lets start from beginning 🙂 it should be first value of start date for current rank ? if so, your formula is correct. if not, it should be first non blank for request No or for all origin data?

[Anonymous]

@az38 

It should be the first non blank value of START date with RANK 1 and I want to insert that value to the first non blank value of FINISH date with RANK 2 at the column RESULT. 

Thanks for your patience!

[az38]

@Anonymous 

if i understand you correct try a column

RESULT23 = 
var _prevRank = 'DATA'[RANK]-1
RETURN
IF( 'DATA'[FINISH] <> BLANK(),
                 CALCULATE(FIRSTNONBLANK('DATA'[START], 1), FILTER(ALLSELECTED('DATA'), 'DATA'[RANK]=_prevRank)),
                 BLANK() )

[Anonymous]

@az38 

Thank you for the solution! But what if the RANK are'nt exactly +1? What if the RANK for START is 7 but the FINISH is 5?
Because inside my full table, the RANK are not exactly sequential.

Thanks for your kind reply!

[az38]

@Anonymous 

you need previous non blank value or the first non blank value or the next? or order doesn't matter - just the firstnonblank? if so

RESULT = 
IF( 'DATA'[FINISH] <> BLANK(),
                 CALCULATE(FIRSTNONBLANK('DATA'[START], 1)),
                 BLANK() )

Sorry, I do not completely understand your idea 🙂

[Anonymous]

@az38 

Sorry, there was a mistake in my reply. * Edited

Yes, the order (RANK) does not  matter in this case. I just want the first non blank value of START at the same row as the first non blank value of FINISH, the second non blank value of START at the same row as the second non blank value of FINISH, etc.

Is it possible? Sorry for the weak explaination of the problem.

[az38 · Accepted solution]

@Anonymous 

maybe column like this

RESULT_COL = 
var _lastFinish = CALCULATE(MAX(DATA[FINISH]),FILTER(ALLSELECTED(DATA),DATA[FINISH]<EARLIER(DATA[FINISH])))
RETURN
IF(DATA[FINISH] <> BLANK(), CALCULATE(FIRSTNONBLANK(DATA[START],1),FILTER(ALLSELECTED(DATA),DATA[START]>_lastFinish)), BLANK())

but be carefull with FIRSTNONBLANK as it returns first value as it stored and not mandatory it is the same with what you see

[Anonymous]

@az38 

Thank you! It works like a charm, people like you really have the "Power" in Power BI!

I will mark it as Solution. Really appreciate it @az38 

[az38]

@Anonymous 

Hi, I'm glad to help you.

You are always welcome in the Community!

Have a good day!