Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

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:
 
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

 
 
Need Help
  • lbendlin's avatar
    lbendlin
    4 years ago

    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's avatar
    tamerj1
    4 years ago

    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's avatar
    Anonymous
    4 years ago

    Thank you so much, @tamerj1

     

    It works perfectly and it is exactly what I needed.

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago
      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        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's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much, @tamerj1

       

      It works perfectly and it is exactly what I needed.