Forum Discussion

Rajesh_Konda's avatar
Rajesh_Konda
Frequent Visitor
3 years ago
Solved

Subtract cost between product and dates

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 ...
Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    You don't need separate queries.

     

     

Rajesh_Konda's avatar
Rajesh_Konda
Frequent Visitor
3 years ago

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's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

    You don't need separate queries.

     

     

    • Rajesh_Konda's avatar
      Rajesh_Konda
      Frequent Visitor
      3 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

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