Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Innerjoin Challenge

 

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.

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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]
)

    Innerjoin-Challenge

     

    Best Regards,
    Dale

4 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        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]
)

        Innerjoin-Challenge

         

        Best Regards,
        Dale