# Help creating a 'delta' chart

• ### Question

• Hello, I am new to Power BI and would like help creating a 'delta' chart based on the Bough / Sold amount for a sample Sales table. I have the following table below:

Based on the table above, I create 2 charts which is 1) Bought Amount Totals per Month and 2) Sold Amount Totals per Month.

Bought Total per Month which sums up the "Buy" column if "Sold = 'NO'" and "BoughtMonth = Month Criteria"

Sales Total per Month which sums up the "Sold" column if "Sold = 'YES'" and "Sold Month = Month Criteria"

I have already created the following above in Power BI. My data source is a SharePoint List, which is based on the Sales Table. Now, I would like to create a "Delta Table" showing the difference between the Sales and Bought Totals per month, something like this (which I created in Excel):

In Excel, I have done this easily because I was able to create manually table and just calculated the deducted values like so:

But in Power BI, I am having difficulty creating the table above. Please note that for reasons, the Sales Table cannot be re-structured anymore. I can add calculated columns and such but I can't remove any of the existing columns there.

Friday, May 26, 2017 5:43 AM

• Hi OCS.New,

In this scenario, you may need to create 3 date table, BoughtDate,SoldDate and Date use below formula:
BoughtDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
SoldDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
Date = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))

Then build relationships as below image,please set relationship between BoughtDate[Date] and Date[Date] to inactive, and set all the cross filter direction to both direction :

Create measure Sale on table SoldDate as below:
Sale = CALCULATE(SUM(Sales[Sold]),FILTER(Sales,Sales[SoldFlag]="YES"))

Create measure Bought on table BoughtDate as below:

Create measure Bo on table Date as below:
Bo = CALCULATE([Bought],USERELATIONSHIP(BoughtDate[Date],'Date'[Date]))

Create measure Sal on table Date as below:
Sal = [Sale]

Create measure FinalMeasure on table Date as below:
FinalMeasure = [Sal]+[Bo]

Add a calculated column YearMonth on table Date as below:
YearMonth = year('Date'[Date])*100+MONTH('Date'[Date])

In the end, you can use visual Matrix to display the YearMonth and FinalMeasure, put YearMonth on column and FinalMeasure on values.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Friday, May 26, 2017 9:42 AM

### All replies

• Hi OCS.New,

In this scenario, you may need to create 3 date table, BoughtDate,SoldDate and Date use below formula:
BoughtDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
SoldDate = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))
Date = CALENDAR (DATE (2017, 1, 1), DATE (2017, 12, 31))

Then build relationships as below image,please set relationship between BoughtDate[Date] and Date[Date] to inactive, and set all the cross filter direction to both direction :

Create measure Sale on table SoldDate as below:
Sale = CALCULATE(SUM(Sales[Sold]),FILTER(Sales,Sales[SoldFlag]="YES"))

Create measure Bought on table BoughtDate as below:

Create measure Bo on table Date as below:
Bo = CALCULATE([Bought],USERELATIONSHIP(BoughtDate[Date],'Date'[Date]))

Create measure Sal on table Date as below:
Sal = [Sale]

Create measure FinalMeasure on table Date as below:
FinalMeasure = [Sal]+[Bo]

Add a calculated column YearMonth on table Date as below:
YearMonth = year('Date'[Date])*100+MONTH('Date'[Date])

In the end, you can use visual Matrix to display the YearMonth and FinalMeasure, put YearMonth on column and FinalMeasure on values.

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

Friday, May 26, 2017 9:42 AM
• Hi OCS.new,

did the suggested solution work for you? If so, please mark it as answer so that others can benefit from it as well. Thx!

Imke Feldmann
MVP Data Platform
TheBIccountant.com

Sunday, June 25, 2017 6:52 AM