topic Re: Subtract two columns from different table per monthly basis using DAX in power bi in DAX Commands and Tips

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

PreetiSahu321 — Wed, 06 Nov 2019 10:50:06 GMT

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?

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

Anonymous — Wed, 06 Nov 2019 11:22:44 GMT

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

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

PreetiSahu321 — Thu, 07 Nov 2019 05:35:51 GMT

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?

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

Anonymous — Thu, 07 Nov 2019 06:21:12 GMT

PreetiSahu321 Please create column in Order table

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

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

PreetiSahu321 — Thu, 07 Nov 2019 06:34:59 GMT

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.

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

kmagat — Thu, 07 Nov 2019 07:48:59 GMT

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$])

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

PreetiSahu321 — Thu, 07 Nov 2019 08:13:20 GMT

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

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

Anonymous — Thu, 07 Nov 2019 09:42:31 GMT

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