Subtract two columns from different table per monthly basis using DAX in power bi

[PreetiSahu321]

Hello Power BI Community Members,

I have two tables as Orders and Invoices. There is one common column in both Orders and Invoices tables i.e. BillDate (Date and Time Datatype). In the Orders table, It has one column named as a Net value whose data type is the Whole Number. Similarly, in the Invoices table, It has a column named as Sales US$ (Whole Number Datatype). As per my requirement, I want to subtract these two columns per monthly basis. Let me explain to you properly.

By using the below screenshot, you can see below are my two tables as Orders and Invoices. I want to subtract the values from Net value to Sales US$ of the current month. As this is November month is going on, So it will subtract the values for the current month only. i.e. (132453-10600=121853)

As I am new to Power BI, I do not have much knowledge about this Power BI DAX rules. Please, Can anyone suggest to me what I have to do in this case?

[Anonymous]

Column = 'Order'[Net Value]-LOOKUPVALUE(Invoice[Sales US],Invoice[Year],'Order'[Year],Invoice[Month],'Order'[Month],0)

[PreetiSahu321]

Hi @Anonymous ,

Can you suggest to me how you have used "Invoice[Year], 'Order'[Year], Invoice[Month], 'Order'[Month]" in the above formula. Because when I am adding this in the new column, it is showing me an error that those columns are not existing in the tables.

That's why I have added this below formula in the column and it is showing me the wrong value as you can see in the below screenshot.

One more thing I want to tell is, For the Invoices table, it is Bill Date whereas, in the Orders table, it is Req.dlv.dt.

As I am new to Power BI, I don't have much knowledge about Dax. Please suggest me what I have to do?

[Anonymous]

@PreetiSahu321  Please create column in Order table 

Column = 'Order'[Net value]-LOOKUPVALUE(Invoice[Sales],Invoice[Bill Date],'Order'[Req.dlv.dt])

[PreetiSahu321]

@Anonymous ,

I have created a new column in the Orders list and applied this formula but it is not working for me and appearing the below error. Also, In the Invoices list, the Bill Date is "MM/DD/YYYY" in format. Similarly, in the Orders list, the Req.dlv.dt is "MM/DD/YYYY" in format.

[kmagat]

Its better to create a date table. Then use the date on the date table for reporting.

Connect the two table using date

ie: 

Measure = SUM(Order[Net Value]) - SUM(Invoices[Sales US$])

[PreetiSahu321]

Hi @kmagat ,

As you said, I have created a Date table and made a relationship with those two tables (Orders and Invoices). But still, it is not showing me the correct value that I am expecting. You can see my sample report by using this below link:

Sample Report 

[Anonymous · Accepted solution]

Measure = 
VAR NetValue = CALCULATE(SUM(Orders[Net value]))
VAR yyyy = MAXX(Orders,Orders[Req.dlv.dt].[Year])
VAR MM = MAXX(Orders,Orders[Req.dlv.dt].[MonthNo])
VAR Sales = CALCULATE(SUM(Invoices[Sales US$]),FILTER(ALL(Invoices),YEAR(Invoices[Bill Date])=yyyy&&MONTH(Invoices[Bill Date])=MM))
RETURN NetValue-Sales

@PreetiSahu321