Desktop

[Slin]

I have two separate data sources, 1 spreadsheet with item by item sales data per store.

StoreID	SKU	Date	Amount
1	91091d46-c523-48d4-9741-61452a150ba3	10/1/2017	1000
1	dadb7f0e-5507-4151-8b05-a0a6f4a6d20c	10/3/2017	2500
1	8ebda452-e0b4-4f73-bbc5-4f5cd5d4a5c9	10/6/2017	5506.73

and another with the forecast by month per store.

StoreID	Month	Forecast
1	October	12000
2	October	5000
3	October	20000

I have a chart that shows the sums up the actual sales in a given month and another that shows the forecast. Is there a way to have a chart that shows the difference between the actual and the forecast within Power BI?

[Ashish_Mathur · Accepted solution]

Hi,

1. In the Forecast Table, create a dummy date column
2. Create a calendar Table.  Extract Month and Year using the MONTH() and YEAR() functions.
3. Create a relationship from the Date columns in both datasets to the Date column in the Calendar table
4. Create a unique StoreID Table
5. Create a relationship from the StoreID column of both datasets to the StoreID column of the table create in point 4 above
6. Drag StoreID form from the StoreID table and Year/Month from the Calendar Table
7. Write the following measures

Total sales = SUM(Data[Amount])

Forecast sales = SUM(Forecast[Forecast])

Variance = [Total sales]-[Forecast sales]

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

1. In the Forecast Table, create a dummy date column
2. Create a calendar Table.  Extract Month and Year using the MONTH() and YEAR() functions.
3. Create a relationship from the Date columns in both datasets to the Date column in the Calendar table
4. Create a unique StoreID Table
5. Create a relationship from the StoreID column of both datasets to the StoreID column of the table create in point 4 above
6. Drag StoreID form from the StoreID table and Year/Month from the Calendar Table
7. Write the following measures

Total sales = SUM(Data[Amount])

Forecast sales = SUM(Forecast[Forecast])

Variance = [Total sales]-[Forecast sales]

Hope this helps.