Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |