Forum Discussion

Harry1980's avatar
Harry1980
Helper I
3 years ago
Solved

Distinct search for a value based on validity date

Hi all,   I have a little challenge where I struggle with and hope to find help here in this great community. What is the challenge?  For a certain material I need to search for the standard price ...
Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Harry1980 

     

    You can try the following methods.

    Measure = 
Var _Selectyearmonth=YEAR(SELECTEDVALUE(BillingDate[Date]))*100+MONTH(SELECTEDVALUE(BillingDate[Date]))
Var _Closedyearmonth=MAXX(FILTER(ALL('Table'),[Material]=SELECTEDVALUE('Table'[Material])&&[Yearmonth]<=_Selectyearmonth),[Yearmonth])
Var _Colsedvalue=CALCULATE(MAX('Table'[Standard Price]),FILTER(ALL('Table'),
           [Yearmonth]=_Closedyearmonth&&[Material]=SELECTEDVALUE('Table'[Material])))+0
Var _Maxyearmonth=MAXX(FILTER(ALL('Table'),[Material]=SELECTEDVALUE('Table'[Material])),[Yearmonth])
Var _Maxvalue=CALCULATE(MAX('Table'[Standard Price]),FILTER(ALL('Table'),
           [Yearmonth]=_Maxyearmonth&&[Material]=SELECTEDVALUE('Table'[Material])))+0
Return
IF(_Selectyearmonth>=_Maxyearmonth,_Maxvalue,_Colsedvalue)

     

     

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

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

Harry1980's avatar
Harry1980
Helper I
3 years ago

Hi,

your amendment in the code seems to work😃. Thank you very much for your support in this. There is only one topic left which creates an issue. My data set shown here in the post was of course kept rather simple in order to simplify the challenge. However, you can imagine that our company data set contains a lot data points as we are selling thousands of different products. What I experience when using your code is that the computation power is not enough. Especially the code below seems to occupy a lot of processing power/memory.

Var _Maxvalue=CALCULATE(MAX('Table'[Standard Price]),FILTER(ALL('Table'),
           [Yearmonth]=_Maxyearmonth&&[Material]=SELECTEDVALUE('Table'[Material])))+0

 Hence, I am wondering whether there is a way to optimize/adjust the code in a way that it is requiring less ressources? 

 

Thank you in advance

Harry