Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
LStoychev
Frequent Visitor

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 @LStoychev 
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

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 @LStoychev 
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] )

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.

 

 

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors