How do I return the first value by order (ignoring blanks)?

[Anonymous]

Hello everyone,

I have the following raw data from the pricing sharepoint:

ID; Date on which the price was modified (updated), Start/End of the price period, the respective price), and a key (ID+Currency).

OriginalID	Modified	Rank	Index	StartDateP1	EndDateP1	Price End P1	StartDateP2	EndDateP2	Price End P2	Key
1111	4.3.2021	4	1	1.1.2021	31.12.2021	9,19				1111EUR
1111	29.21.2021	2	2	1.1.2021	31.12.2021	9,19	1.1.2022	31.12.2022	9,19	1111EUR
1111	8.4.2022	3	3	1.1.2021	31.12.2021	9,19	1.1.2022	31.12.2022	9,17	1111EUR
1111	12.4.2022	1	4	31.12.2020	30.12.2021	9,19		31.12.2022	9,17	1111EUR
2222	16.3.2022	4	5	31.12.2021	30.12.2022	50,01	31.12.2022	31.12.2023	52,71	2222EUR
2222	23.3.2022	3	6	31.12.2021	30.12.2022	51,71	31.12.2022	31.12.2023	52,71	2222EUR
2222	29.3.2022	2	7	31.12.2021	30.12.2022	51,71	31.12.2022	31.12.2023	52,71	2222EUR
2222	18.4.2022	1	8	31.12.2021	30.12.2022	51,71	31.12.2022	31.12.2023	52,71	2222EUR

I have created a formula that finds the 22' price from each price update:

22' Price = SWITCH(TRUE (),

[EndDateP1]>Date(2022,01,01) && [StartDateP1]<Date(2022,12,31), [Price End P1],

[EndDateP2]>Date(2022,01,01) && [StartDateP2]<Date(2022,12,31), [Price End P2].....)

It works perfectly:

What I am trying to create now is a formula that finds the 22' original price (the 22' price that was valid on 01.01.2022). 

I am trying to achieve the result in the example below (the original price to be populated in each row):

 

 The problem is that in some cases the first row in the 22' Price is Blank, because there was no 22' price in the beginning. It means that I need the first non blank price.

Do you have any ideas?

Thanks,

Leo

[lbendlin · Accepted solution]

You need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.

Also look into FIRSTNONBLANKVALUE - that might save you a step.

Or use the pedestrian way

22 Price = 
var m = min('Table'[Modified])
return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to sample file with the solution https://www.dropbox.com/t/0WkCrxsXlYxewGbb

22' Original Price = 
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[OriginalID] ) )
VAR NonBlankTable = FILTER ( CurrentIDTable, Data[22' Price] <> BLANK ( ) )
VAR FirstRecord = TOPN ( 1, NonBlankTable, Data[Index], ASC )
RETURN
    MAXX ( FirstRecord, Data[22' Price] )

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please refer to sample file with the solution https://www.dropbox.com/t/0WkCrxsXlYxewGbb

22' Original Price = 
VAR CurrentIDTable = CALCULATETABLE ( Data, ALLEXCEPT ( Data, Data[OriginalID] ) )
VAR NonBlankTable = FILTER ( CurrentIDTable, Data[22' Price] <> BLANK ( ) )
VAR FirstRecord = TOPN ( 1, NonBlankTable, Data[Index], ASC )
RETURN
    MAXX ( FirstRecord, Data[22' Price] )

[Anonymous · Accepted solution]

Thank you so much, @tamerj1

It works perfectly and it is exactly what I needed.

[lbendlin]

I would recommend you unpivot your data before proceeding.  Or use FIRSTNONBLANK if you are in a hurry.

[Anonymous]

Thank you @lbendlin 

I created the following formula:

22' Original Price =

CALCULATE(FIRSTNONBLANK('AllRegions'[22'Price],'AllRegions'[22'Price]<>BLANK()),

FILTER(ALLEXCEPT('AllRegions','AllRegions'[Key]),

MAX(AllRegions[Index])))

The problem is that it finds the minimum price for each item, but I need the price that was valid in the first non blank period.

Any ideas what am I doing wrong?

[lbendlin · Accepted solution]

You need to apply FIRSTNONBLANK to the date. Then use that date as the filter to get the price at that date.

Also look into FIRSTNONBLANKVALUE - that might save you a step.

Or use the pedestrian way

22 Price = 
var m = min('Table'[Modified])
return CALCULATE(sum('Table'[Price End P2]),'Table'[Modified]=m)