Lookup does not work

[GracieLeeMonroe]

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 periods to release the revenue. I created the following measure:

MEASURE =

VAR Category = TABLE[Revenue Model Category]

VAR boolean_LicTypeOther = TABLE[License type]= "Other"

VAR boolean_LicTypePrptl = TABLE[License type] = "Perpetual"

VAR boolean_LicTypeFlex = TABLE[License type] = "Flex"

VAR boolean_Schuetz_Prptl_Core = TABLE[License type] = "Perpetual" && TABLE[Life cycle] = "New" && TABLE[Product type] = "Core Version"

VAR boolean_Module = TABLE[Product type] = "Module" && TABLE[License type] = "Perpetual"

VAR FOT_Adjusted = TABLE[FOT_Adjusted]

VAR UpgradeContract = TABLE[License type] = "Perpetual" && TABLE[Life cycle] = "Extended"

VAR boolean_serialno_Core = CALCULATETABLE(TABLE, ALL(TABLE[serialNumber], TABLE[Product type], TABLE[FOT_Convention]),TABLE[serialNumber] <> Blank (),TABLE[Product type] = "Core Version")

VAR boolean_serialno_Module = CALCULATETABLE(TABLE, ALL(TABLE[serialNumber], TABLE[Product type], TABLE[FOT_Convention]),TABLE[serialNumber] <> Blank (),TABLE[Product type] = "Module")

VAR boolean_serialno = CALCULATETABLE(TABLE, FILTER(TABLE, TABLE[serialNumber] <> Blank ()))

VAR boolean_fot_cv = LOOKUPVALUE(TABLE[Date], TABLE[serialNumber],  DISTINCTCOUNTNOBLANK([serialNumber]) , TABLE[Product type], "Core Version")

VAR boolean_fot_module = LOOKUPVALUE(TABLE[Date], TABLE[serialNumber], DISTINCTCOUNTNOBLANK([serialNumber]), TABLE[Product type], "Module")

VAR boolean_Module_New = TABLE[Product type] = "Module" && TABLE[Life cycle] = "New"

RETURN

    SWITCH (

            True,

            Category IN {"xx", "yy", "zz" },

                SWITCH (

                    TRUE,

                    boolean_LicTypeOther, 1,

                    boolean_LicTypePrptl, 12,

                    boolean_LicTypeFlex, 12,

                    boolean_Module_New && NOT ISBLANK (FOT_Adjusted), 12 - DATEDIFF(boolean_fot_module, boolean_fot_cv,  MONTH),

                        12),

            Category IN { "aa"},

                SWITCH (

                    TRUE,

                        boolean_Schuetz_Prptl_Core, 37,

                        boolean_Module && ISBLANK (Fot_Adjusted), 18,

                        boolean_LicTypeOther, 1,

                        boolean_Module && NOT ISBLANK (FOT_Adjusted) && TABLE[Life cycle] = "New", 37,

                        12),

            Category IN { "bb" },

                SWITCH (

                    TRUE,

                       UpgradeContract, 1,

                        48),

            Category IN { "cc" },

                SWITCH (

                    TRUE,

                        UpgradeContract || boolean_LicTypeOther, 1,

                        boolean_LicTypeFlex,  12,

                        1),

            Category IN { "dd" },

                SWITCH (

                    TRUE,

                        boolean_LicTypeOther || boolean_LicTypePrptl, 1,

                        12),

            BLANK ()

            )

I am not sure if I am on the right track, but I am getting nowhere. Now I get the typical message Function "'LOOKUPVALUE' does not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values." Since SerialNumber and Product type are both text, I assume the culprit is the date, but I do not know how to convert this actually. Does someone has an idea? And i searched this forum and the internet in general to no avail. 

Currently:

Date	Product Type	LIfe Cycle	SerialNumber	MEASURE
02/01/2021	Core Version	New	12340815	12
01/01/2022	Module	New	12340815	12
02/01/2022	Core Version	Extended	12340815	12
02/01/2022	Module	Extended	12340815	12

Should be:

Date	Product Type	LIfe Cycle	SerialNumber	MEASURE
02/01/2021	Core Version	New	12340815	12
01/01/2022	Module	New	12340815	1
02/01/2022	Core Version	Extended	12340815	12
02/01/2022	Module	Extended	12340815	12

I also think that I have to consider the fact that the Core Version might be older than 1 year so I need the latest FOT_Convention date for this - if that makes sense. the result for the measure should be between 0 and 12.

Pls  let me know if you needf any additional information!

Thanks everyone!

[GracieLeeMonroe]

Hi @v-janeyg-msft 

I prepared the excel sample extract:

https://docs.google.com/spreadsheets/d/12tZKzhcWnOzSLOXz9n_7MjL_gP4N3-qn/edit?usp=sharing&ouid=11683...

I know you do prefer PBIX, but the source file is on sharepoint and I cannot change that data of course.

I did highlight the calculated columns, the column I try to calculate is AU. We can do a screen sharing as well if necessary.

Regards

Angie

[v-janeyg-msft]

Hi, @GracieLeeMonroe 

I don't have permission to open the link.

[GracieLeeMonroe]

Hi @v-janeyg-msft 

sorry, my bad. I changed the settings plus imported the excel into a sample PowerBI file:

https://drive.google.com/file/d/1soJ0IG0gnLW_cndyOPc6HfyehB0DQ-9Y/view?usp=sharing

Just let me know if this is now working. 

Thanks.

Regards

Angie

[v-janeyg-msft]

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]

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]

@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

[GracieLeeMonroe]

Hi @v-janeyg-msft 

I do understand, but it is company data and confidential, sorry.

FOT is an abbreviation for First Online Time which defines for certain revenue streams the start of revenue recognition. There are several columns (FOT Adjusted, FOT_Convention) to adjust this date so it fits the needs - hope this helps. This file is generated each month and then imported into PowerBI.

I do have License Type Perpetual, Flex and Other, Product Type Core Version, Core Version - Other, Module and Other. I do need a Core Version or Core Version Other at a minimum, then the customer can add Modules at any time, but the module follows the Core Version in terms of revenue recognition. The DAX formula I try to create should calculate this shorter revenue recognition period for modules added later in time. The Licenses (Perpetual and Flex) are split in License and Support, Support is deferred over 12 months. 

I continued to work on my formula, but still do not get the result I want:

VAR boolean_fot_cv = Calculate(MAX(IDL_Total[FOT_Convention]), Filter(IDL_Total,IDL_Total[serialNumber]<>BLANK() && IDL_Total[Product type] = "Core Version" ))

VAR boolean_fot_module = Calculate(MIN(IDL_Total[FOT_Convention]), Filter(IDL_Total,IDL_Total[serialNumber]<>BLANK() && IDL_Total[Product type] = "Module" ))

I am happy to share an excel file with you and any DAX Command for a calculated column/measure you need. But I would need to delete certain data (Customer Name, Product Name etc.) which I guess yo do understand.

Regards

Angie

[v-janeyg-msft]

@GracieLeeMonroe    Waiting for you to share and remember to hide privacy.

[v-janeyg-msft]

Hi, @GracieLeeMonroe 

I checked your measure, trying to find the logic, I found that your code is very verbose, and the two codes of lookupvalue are indeed wrong, but I don't know what you want.

So I need a complete and valid sample along with your calculation logic and expected results to help you modify measure. If you can provide, I'll help you streamline your code and modify it correctly.

Best Regards,
Community Support Team _ Janey

[GracieLeeMonroe]

Hi @v-janeyg-msft 

thanks for your answer. I did not ignore you but was working on my huge excel files last week which actually are to be replaced by PowerBI. Regarding the sample, what exactly do you need? An extract from PowerBI in xls-format? I would need to remove/replace confidential data of course.....

Thanks!

Regards

Angie

[v-janeyg-msft]

Hi, @GracieLeeMonroe 

Check this:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

If you don't give the complete dummy data, it's hard for me to judge the logic of your measure.

Like what's FOT_Adjusted?

And your code is too verbose, I don't need to read this, I just need you to tell me the complete calculation logic, so I can create a new for you.

Best Regards,
Community Support Team _ Janey