Merge queries in different format

[cocomy]

Hi

I want to merge tables in different format.

I tried query,  naturalleftouterjoin etc but I can not build relationships to work .

When I try to comvert from A to C, duplication occurs. Can you suggest how should I do it to achieve Table E in the end?

• Table A - daily forecast by month by branch
• Table B- calender (to convert from monthly to daily)
• Table C = Table A + B , daily forecast by day by branch
• Table D - actual daily sales by branch *if no sales, no record in the table
• Table E = Table C+D , actual daily sales by branch with daily forecast

[MFelix · Accepted solution]

Hi @cocomy,

I was abble to replicate your information follow the steps below:

• Create a new table Branchd

   Branch   ID

Tokyo	1
Osaka	2
Nagoya	3

• Related the following tables:
  • Branch - Daily Sales : relate by branch
  • Branch - Daily forecast: relate by branch
  • Calendar - Daily Sales: relate by Date
• Create the measure below:

Measure created on Daily Forecast Table

Daily Forecast Total =
VAR Select_Month =
    MAX ( Calendar[Monthly Date] )
RETURN
    CALCULATE (
        SUM ( 'Daily Forecast'[Daily Forecast] );
        'Daily Forecast'[Monthly Date] = Select_Month
    )


Measure created on Daily Sales Table

Actual totals = SUM('Daily Sales by branch'[Actual])

• Make your table visual with the following columns / measures:
  • Date (Calendar Table)
  • Branch (Branch Table)
  • Actuals totals (measure)
  • Daily Forecast Total (measure)

As you can see the result is what you need to have, I have calculated measure because if you need you can calculate differences and percentages over objective.

Here is a link to PBIX file with the examples and measures.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @cocomy,

I was abble to replicate your information follow the steps below:

• Create a new table Branchd

   Branch   ID

Tokyo	1
Osaka	2
Nagoya	3

• Related the following tables:
  • Branch - Daily Sales : relate by branch
  • Branch - Daily forecast: relate by branch
  • Calendar - Daily Sales: relate by Date
• Create the measure below:

Measure created on Daily Forecast Table

Daily Forecast Total =
VAR Select_Month =
    MAX ( Calendar[Monthly Date] )
RETURN
    CALCULATE (
        SUM ( 'Daily Forecast'[Daily Forecast] );
        'Daily Forecast'[Monthly Date] = Select_Month
    )


Measure created on Daily Sales Table

Actual totals = SUM('Daily Sales by branch'[Actual])

• Make your table visual with the following columns / measures:
  • Date (Calendar Table)
  • Branch (Branch Table)
  • Actuals totals (measure)
  • Daily Forecast Total (measure)

As you can see the result is what you need to have, I have calculated measure because if you need you can calculate differences and percentages over objective.

Here is a link to PBIX file with the examples and measures.

Regards,

MFelix

[cocomy · Accepted solution]

Thank you very much for detailed explanation. It is what I have been looking for ages.

All the best,

coco