Forum Discussion

Ireneliu's avatar
Ireneliu
Regular Visitor
7 years ago

Difference between two tables

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!

7 Replies

    • Ireneliu's avatar
      Ireneliu
      Regular Visitor
      7 years ago

      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's avatar
        Stachu
        Community Champion
        7 years ago

        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