Innerjoin Challenge

[Anonymous]

Hello Power friends, some help, please.

I have two tables as below:

Planned Sales

id_store, id_department, date, sales on month (planned)

01, 222,01/01/2018, 100

01, 222,01/02/2018, 120

01, 222,01/03/2018, 130

(...)

01, 222, 01/12/2018, 150

01, 333,01/01/2018, 100

01, 333,01/02/2018, 120

01, 333,01/03/2018, 130

(...)

01, 333, 01/12/2018, 150

***suppose we are on march

Sales

id_store, id_department, date, sales on month (real)

01, 222,01/01/2018, 100

01, 222,01/02/2018, 50

01, 222,01/03/2018, 70

01, 333,01/01/2018, 80

01, 333,01/02/2018, 145

01, 333,01/03/2018, 120

I need a table that merges sales table and planned sales in order to be able to simulate the result of the year by id_store and id_department (sales until now and planned sales).

The new merged table must consider that when there are no sales made (after march) the column of sales have to be filled with planning sales.

I need it in DAX because the date collum is a calculated collum ans doesn't appear in Power Query.

Thanks a lot for your time.

[Phil_Seamark]

Hi @Anonymous

So do you want your new table to have 1 row per day/store/department?

And if there is data for it in the Sales table, then use that otherwise, use data from the Planned Sales table?

[Anonymous]

Hi @Phil_Seamark,

The merged final table could be like that:

id_store, id_department, date (DD/MM/YYYY), sales on month (real or planned) ou both (two diferrents colluns)

01, 222, 01/01/2018, 100

01,111, 01/01/2018, 120

01, 111, 02/02/2018, 212

02, 333, 01/01/2018, 200

02, 444, 02/01/2018, 120

(...)

The date of planed sales table begins in 01/01/2018 and ends in 31/12/2018.

Thanks again for you time.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Try this formula, please. Please also download the demo from the attachment.

Table =
SELECTCOLUMNS (
    ADDCOLUMNS (
        'plan',
        "New Col",
        VAR salesValue =
            LOOKUPVALUE (
                sales[Sales],
                sales[id_store], [id_store],
                sales[id_department], [id_department],
                sales[date], [date]
            )
        RETURN
            IF ( ISBLANK ( salesValue ), [Planed], salesValue )
    ),
    "id_store", [id_store],
    "id_department", [id_department],
    "date", [date],
    "Sales Planed or Real", [New Col]
)

Best Regards,
Dale

[Anonymous]

Great solution @v-jiascu-msft. Thanks a lot.