Forum Discussion

abc_777's avatar
abc_777
Solution Specialist
3 years ago

hi

hi   I have a sales table where i have value sold and return. When  sold invoice number is normal a number but when return then there is CR at beginning and CR or return invoice number is not sam...
Need Help
v-luwang-msft's avatar
v-luwang-msft
Community Support
3 years ago

Hi abc_777 ，

First ,add index ,then use the below dax to create a new column:

test = IF('Table'[Net Amount]<0,'Table'[Net Amount]+CALCULATE(MAX('Table'[Net Amount]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])-1)),if(CALCULATE(MAX('Table'[Net Amount]),FILTER(ALL('Table'),'Table'[Index]=EARLIER('Table'[Index])+1))<0,0,'Table'[Net Amount]))

 

 

Then create the below measure:

avg = SUM('Table'[test])/CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[test]>0))

Output :

 

 

Best Regards

Lucien

  • abc_777's avatar
    abc_777
    Solution Specialist
    3 years ago

    v-luwang-msft 

     

    thanks for your efforts. really great. would please help me find where there error is in my calculative column

    invoice value that is fully refund with CR should be both be ) but mine not doing that. PLease help

     

    test = IF('bm_retail_t ssummary'[NET_AMT] < 0,
                    'bm_retail_t ssummary'[NET_AMT] +
                            CALCULATE(
                                    MAX(
                                        'bm_retail_t ssummary'[NET_AMT]),
                                                FILTER(ALL('bm_retail_t ssummary'),'bm_retail_t ssummary'[Index] =
                                                    EARLIER('bm_retail_t ssummary'[Index]) - 1)),
                            if(CALCULATE(
                                MAX('bm_retail_t ssummary'[NET_AMT]),
                                    FILTER(ALL('bm_retail_t ssummary'),
                                        'bm_retail_t ssummary'[Index] = EARLIER('bm_retail_t ssummary'[Index]) + 1)) < 0, 0, 'bm_retail_t ssummary'[NET_AMT]
                                        )
                                        )
    • abc_777's avatar
      abc_777
      Solution Specialist
      3 years ago

      I think we made a mistaked

       

      221104 120
      CR999999 -120

       

      not always be together. invoice number and corrorpondance invoice CR could be in different place, could be after fifteen thousand or tweenty thousand rows. could be anywhere in same table.

       

      in that case - 1 is not right.

       

      we could say when CR Nat Amount matches with invoice Net Amount and  in same invoice date (I didnt mention it in my example table but you can create it in yours) then make CR Nat Amount and Net Amount of them 0

       data is something like this its over 3 milions of data with invoice and CR Net amount and Invoice Date

       

      • abc_777's avatar
        abc_777
        Solution Specialist
        3 years ago

        v-luwang-msft 

         

        could you tell me how i can i make ) when CR is full refunede. if thats the thing is hard to do

         

        thanks