How to Lookup or Return text for Selected Date

[Anonymous]

Hi all,

Please see below screen-cap, I would like to return the Pricing Model for selected month.

For example, I have selected date (201904) and the SKU 45779A.

Can I use lookupvalue or calculate function to return the value "Regular" (Pricing Model for 201904)?

(Tried to use below, but will error)

Pricing Model = LOOKUPVALUE('Wayfair Net WS Price'[ Pricing Model],'Wayfair Net WS Price'[Year_Month],[Current Month])

I hope I can explain myself clearly. Please let me know if I need to upload the whole power bi file

[Anonymous]

Are you using a measure or a calculated column

[Anonymous]

I believe I need to use measure.....But I am quite new to PowerBI and still not 100% understand the logic

[Zubair_Muhammad · Accepted solution]

@Anonymous 

Try this MEASURE

Wayfair Pricing Model =
VAR mymonth = [Current Month]
RETURN
    CONCATENATEX (
        CALCULATETABLE (
            VALUES ( 'Wayfair Net WS Price'[ Pricing Model] ),
            'Wayfair Net WS Price'[Year_Month] = mymonth
        ),
        [ Pricing Model],
        ","
    )

[Anonymous]

Hi Zubair_Muhammad,

Great thanks. It is working prefectly.

I will learn the CONCATENATEX

[Zubair_Muhammad]

@Anonymous 

Concatenatex is not necessary if there are no duplicates.

If 2 or more models meet the criterion then CONCATENATEX will avoid the error " a table of multiple values was supplied"

So if you use following, it should work as well

Wayfair Pricing Model =
VAR mymonth = [Current Month]
RETURN
    CALCULATETABLE (
        VALUES ( 'Wayfair Net WS Price'[ Pricing Model] ),
        'Wayfair Net WS Price'[Year_Month] = mymonth
    )

[CheenuSing]

Hi @Anonymous 

Can you please upload the pbix file in One Drive / Google Drive and share the link here.

Also the output you expect for your scenario.

Cheers

CheenuSing

[Anonymous]

Hi CheenuSing,

Thanks for your help, below is the pbix link.

https://drive.google.com/open?id=1AtDMI5KqBwP1m_-xqsYh6ov6bMQh7dPC

Please go to "Wayfair Analytics" tab and I would like to add "Pricing Model" column  to the bottom right hand corner table

I store the Pricing Model data in the table "Wayfair Net WS Price" and created a measure "Wayfair Pricing Model" under "Product Margin" (but is not working now).

The SKU # 45779A is a good example as a demo as it change from "EDLP" to "Regular" in 201904

Year	Month	Sales Unit	Avg Sell $	Wholesales $	Net Wholesales $	Wayfair Margin $	Wayfair Margin %	Pricing Model
2019	January		$295.14	$184.44	$175.22	$119.93	40.63%	EDLP
2019	February		$307.80	$196.06	$186.26	$121.55	39.49%	EDLP
2019	March	7	$297.57	$196.06	$186.26	$111.31	37.41%	EDLP
2019	April	6	$293.17	$260.96	$227.03	$66.14	22.56%	Regular
2019	May					N/A	N/A	N/A
2019	June					N/A	N/A	N/A
2019	July					N/A	N/A	N/A
2019	August					N/A	N/A	N/A
2019	September					N/A	N/A	N/A
2019	October					N/A	N/A	N/A
2019	November					N/A	N/A	N/A
2019	December					N/A	N/A	N/A

Just free feel to let me know if you need further explanation on the dashboard structure. Thanks.