cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

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

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

3 ACCEPTED SOLUTIONS
Super User

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)``````
Super User

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
Not applicable

Thank you so much, @tamerj1

It works perfectly and it is exactly what I needed.

5 REPLIES 5
Super User

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
Not applicable

Thank you so much, @tamerj1

It works perfectly and it is exactly what I needed.

Super User

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

Anonymous
Not applicable
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?
Super User

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)``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.