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.

v-zhangti's avatar
v-zhangti
Community Support
3 years ago

Hi, Harry1980 

 

You can try the following methods.
Measure:

Yearmonth = SELECTEDVALUE('Table'[Year])*100+SELECTEDVALUE('Table'[Period])
Measure = 
Var _Selectyearmonth=YEAR(SELECTEDVALUE(BillingDate[Date]))*100+MONTH(SELECTEDVALUE(BillingDate[Date]))
Var _Closedyear=MAXX(FILTER(ALL('Table'),[Material]=SELECTEDVALUE('Table'[Material])&&[Yearmonth]<=_Selectyearmonth),[Year])
Var _Closedmonth=MAXX(FILTER(ALL('Table'),[Material]=SELECTEDVALUE('Table'[Material])&&[Yearmonth]<=_Selectyearmonth),[Period])
Return
CALCULATE(SUM('Table'[Standard Price]),FILTER(ALL('Table'),
          [Year]=_Closedyear&&[Period]=_Closedmonth&&[Material]=SELECTEDVALUE('Table'[Material])))+0

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 Charlotte,

 

thank you for your reply. Your suggestion does not exactly match what I would like to achieve. I see now that I should have provided more info.  I have created another way. It works fine as long as for the year a material was billed also a standard price  record exists/was created. For example for materials A-D the last record were created in 2021. Then my formula finds correct standard price until billing date 31.12.2021. But from 01.01.2022 the result is blank even though the last records from 2021 should still be valid for 2022 and 2023. For example for Material A the record valid from 02 2021 is the last available record and hence this would be still valid in case something was billed in 2022 or 2023. In this case it would be 2 for material

A in 2022 and 2023

 

Is there any smart way making it work also for 2022 or 2023?

 

Thank you in advance

 

Harry

 

used expression:

 

Before I have derived the code below I have added a month and a year column and have also increased the date range in table billing date (from 01.01.2019-31.12.2023) in order to be able to verify whether my code works

 

Valid Price =

VAR _targetmonth = max(BillingDate[Month])
VAR _targetyear = max(BillingDate[Year])  
VAR _material = MAX('Table'[Material])
VAR _table =    
FILTER(
        'Table',
        'Table'[Material]=_material
            &&'Table'[Year] = _targetyear
            &&'Table'[Period]<= _targetmonth
    )

VAR _month =
MAXX(
    _table,
    'Table'[Period]
)
RETURN
MAXX(
    FILTER(_table, 'Table'[Period]=_month),
    'Table'[Standard Price]
)
 
Below is desired outcome for various billing dates
MaterialBilling dateStandard Price
A31.01.20232
B31.01.20233
C31.01.20237
D31.01.20239
A31.01.202111
B31.01.20214
C31.01.20210
D31.01.20216
A31.10.20190
B31.10.20190
C31.10.20190
D31.10.20196

 

  • v-zhangti's avatar
    v-zhangti
    Community Support
    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.