Forum Discussion

GracieLeeMonroe's avatar
GracieLeeMonroe
Frequent Visitor
4 years ago

Lookup does not work

All I am trying to get a date by certain criterias (serial no. and Product type) since I need to calculate the difference between those dates in month in order to calculate the correct quantity of p...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, GracieLeeMonroe 

     

    Can you tell me for this formula what do you want to get? Your measure is too messy and I can't see a complete logic from your description, It's hard for me to get started without logic.

     

    • GracieLeeMonroe's avatar
      GracieLeeMonroe
      Frequent Visitor
      4 years ago

      Hi v-janeyg-msft 

      the purpose of the calculated column in general is to define the revenue recognition period. A customer acquires the core version at a certain time, and then adds modules if needed. The module has to follow the core version in terms of revenue recognition period, the link is the serial number. Revenue Recognition starts upon FirstOnlineTime (FOT), there are several calculated columns to define the FOT date which should be used for start of revenue recognition, the final one is named "FOT Convention". So a customer purchased a core version in 12/2020 with a FOT Convention date of 01/2021 and in 02/2022 he adds a module to this license. The revenue for the module then should be recognized over 11 months. Hope this makes sense, since this is the overall logic.
      With the formulas I just tried to determine those two dates (FOT Convention for the Core Version which should be in the example above 01/2022 and the addition of the module in 02/2022) so I can calculate the difference in months further below.
      In the sample data you have, there is one line with an amount of 4.25 and that should be a period of 1 month - if that helps.

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Community Support
        4 years ago

        GracieLeeMonroe 

         

        To be honest, the sample data you provided is not representative and can't be used to test. And what you say is not on point, I'm really struggling to understand.  So it's hard for me to help you.

         

        All I can say is, here you need to return two dates, you need to modify your code, you don't need to use lookupvalue at all. 

        Like this:

        var a =
MAXX (
    FILTER (
        ALL ( table ),
        [serialNumber] = "..."
            && [Product type] = "Core Version"
            && other conditions
    ),
    [Date]
)

        Best Regards,
        Community Support Team _ Janey