Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
Column = 'Order'[Net Value]-LOOKUPVALUE(Invoice[Sales US],Invoice[Year],'Order'[Year],Invoice[Month],'Order'[Month],0)
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?
@PreetiSahu321 Please create column in Order table
@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.
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$])
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:
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
14 |
User | Count |
---|---|
42 | |
39 | |
33 | |
19 | |
18 |