Difference between two tables

[Ireneliu]

Hello Everyone,

I have a question about the diference between two tables.

A Table is for department's actual results.

B Table is for department's budget.

When the actual results have a value, I can't get a correct value, even no value.

I want to have values by month.

Please suggest the DAX measure.

Thank you!

[Stachu]

can you share your current syntax, and the underlying tables?

as described here
How to Get Your Question Answered Quickly 

[Ireneliu]

Hello @Stachu ,

It was my first time publishing a question here, sorry for the incomplete information.

Columns for Table A are Department、AccountName、Date、accountingcurrencyamount

Columns for Table B are Department、AccountName、Date、budget

The relationship between two tables is "AccountName".
(Cardinality:many to many. Cross filter direction:both.)

*Measure for actual results = CALCULATE(SUM('A'[accountingcurrencyamount]),

FILTER('A',[AccountName]="Salary"|| [AccountName]="Retirement pension")

*Measure for budget = CALCULATE(SUM('B'[departmentbudget]),

FILTER('B',[AccountName]="Salary"|| [AccountName]="Retirement pension")

I used "FILTER" to choose the AccountName I need.

*Measure for difference = [Measure for actual results]-[Measure for budget]

I tried to change the relationship become Date yesterday, and got the following result.

I still can't get the right values when actual results have value...

[Stachu]

do the following:

1) create a new table for calendar, e.g.

Calendar = CALENDARAUTO()

2) mark the Calendar tables as Date table
3) create 1:many  joins between Calendar table and both budget and actuals

4) use the month from calendar table in the visual

5) set the join between A & B tables to be done on Department

[Ireneliu]

I followed up the steps you suggested but having a trouble in step 3.

The relationship between Calendar table and both budget and actuals can't be active at the same time.

[Stachu]

Hmm, that's probably because of the many:many relationship

can you add a new table for Departments, e.g. like this:

Departments =
DISTINCT (
    UNION ( VALUES ( TableA[Departments] ), VALUES ( TableB[Departments] ) )
)

deactivate/remove your existing many:many relationship between Actuals and Budgets

create new 1:many relationships from Departments to tables A and B

do the same for the Calendar table

[Ireneliu]

Sorry for late reply.

I tried to change the relationships.

However, when I choose Retirement pension to make sure the difference value is right or not,
finding out the value of Actural results of Salary is included.

Is it because of there is no relationship about AccountName between table A and B?

[Ireneliu]

I create a new table for Account Name and make 1:many relationships from Account Name to tables A and B.

Finally,getting the right value.