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: 

CostServiceNameDate
20XYZ04-04-2023
30ABC05-04-2023
50XYZ05-04-2023
10ABC03-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

 

Need Help

6 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    3 years ago

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

  • 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.