Subtract cost between product and dates

[Rajesh_Konda]

Hi All,

How can i Subtract cost bet ween dates based on product.

Ex: 

Cost	ServiceName	Date
20	XYZ	04-04-2023
30	ABC	05-04-2023
50	XYZ	05-04-2023
10	ABC	03-04-2023

now i want to subtract cost by dates and display results accoring to Service name, have lot servicenames need to subscract for all values and display date wise in visual as total and service name wise.

Results should be like XYZ is 30 & ABX is 20...etc on table

[Rajesh_Konda]

Hi @lbendlin  As per my requirment with single query thought can not fullfill my requirment. So i have duplicated query, query1 and query2 both these have same data.

With in query2 i have added a DAX function as 

Difference = (CALCULATE(SUM(Query2[Cost[USD]]])) - CALCULATE(SUM(Query1[Cost[USD]]])))

Then i try to display data on visuals this was working fine when i select between dates the difference cost displayed correctly. But when drill down to  ServiceName the results showing incorrectly as same amount is displaying for all the Services(cross checked in DB, both values are not matching).  Can you help me how i can make it work proplerly for based on services. Thanks in advance.

[lbendlin · Accepted solution]

You don't need separate queries.

[Rajesh_Konda]

Thanks a lot @lbendlin , the query working but the results are displaying wrong.  Have just compared it on Matrix, below date the value shown as accurate as 6.57 which is correct, but on total it is shown as 18.96 don't know why it is showing wrong. Even i tried adding it on multiple visuals total value is displayed 18.96.

I'm doing an wrong can you please suggest. 

Measure =

var mxd = MAX(CostDataForDate2[DATE])

var pud = CALCULATE(MAX(CostDataForDate2[DATE]), CostDataForDate2[DATE]<mxd)

var cs = CALCULATE(SUM(CostDataForDate2[Cost[USD]]]), CostDataForDate2[DATE] = pud)

Return IF(ISBLANK(cs),BLANK(),(SUM(CostDataForDate2[Cost[USD]]])-cs))

have copy pasted your data.

[lbendlin]

You will have to explain what you want the Total to display. It may not even be required/meaningful in your case.

[Rajesh_Konda]

Sorry  @lbendlin ,  I am actually new to power BI. I thought of displaying Total results in a  visuals(card, bar graph, pie) but when i add Calculated dax column to any visuals the results shown are incorrect(compared results in DB). But on the matrix table the values are shown accurate for column. I Appreciate your help Thanks a lot.

[lbendlin]

Sounds straightforward. What have you tried and where are you stuck?