Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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).

1.jpg

OriginalIDModifiedRankIndexStartDateP1EndDateP1Price End P1StartDateP2EndDateP2Price End P2Key
11114.3.2021411.1.202131.12.20219,19   1111EUR
111129.21.2021221.1.202131.12.20219,191.1.202231.12.20229,191111EUR
11118.4.2022331.1.202131.12.20219,191.1.202231.12.20229,171111EUR
111112.4.20221431.12.202030.12.20219,19 31.12.20229,171111EUR
222216.3.20224531.12.202130.12.202250,0131.12.202231.12.202352,712222EUR
222223.3.20223631.12.202130.12.202251,7131.12.202231.12.202352,712222EUR
222229.3.20222731.12.202130.12.202251,7131.12.202231.12.202352,712222EUR
222218.4.20221831.12.202130.12.202251,7131.12.202231.12.202352,712222EUR

 

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:
2.jpg
 
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):
 
 3.jpg

 

 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

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)

View solution in original post

tamerj1
Super User
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] )

View solution in original post

Anonymous
Not applicable

Thank you so much, @tamerj1

 

It works perfectly and it is exactly what I needed.

View solution in original post

5 REPLIES 5
tamerj1
Super User
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.

lbendlin
Super User
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.
 
6.jpg
 
Any ideas what am I doing wrong?

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)

Helpful resources

Announcements
Europe Fabric Conference

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.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.