Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Return applicable price within time frame

Hello! I'm still getting my way around BI and some simple things cause me to stumble. So I have a price list with different price types. Date column of the price lists sets time frame within which ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Anonymous ,

     

    Would you please try the following measure, if it doesn't work , please explain more about your expected output.

     

     

    PRICE LIST REF = 
var PriceValue = CALCULATE(AVERAGE(PRICE_LIST[PRICE]),FILTER(ALL(DATES),DATES[DATES] in VALUES(DATA_SALES[SALES DATE])))
return
PriceValue

     

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

  • Anonymous's avatar
    Anonymous
    5 years ago

    Cracked it. Turned out to be way simpler than I thought

     

     

    PRICE LIST REFERENCE =
var mindate = MIN(DATES[DATE])
var priceref= CALCULATE(LASTNONBLANK(PRICES[PRICE],[PRICE]),FILTER(ALL(DATES[DATE]),DATES[DATE]<=mindate))

return
priceref

     

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi Anonymous ,

 

Would you please try the following measure, if it doesn't work , please explain more about your expected output.

 

 

PRICE LIST REF = 
var PriceValue = CALCULATE(AVERAGE(PRICE_LIST[PRICE]),FILTER(ALL(DATES),DATES[DATES] in VALUES(DATA_SALES[SALES DATE])))
return
PriceValue

 

 

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Best Regards,

Dedmon Dai

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you very much!

Still testing, but beems to work like a charm with a minor alteration of using AVERAGEX instead of an AVERAGE.