Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate difference between invoice amount not based on date

I'm looking for a way to calculate the difference between invoice amount, but not based on dates (month), but based on order of the invoice. So the difference between each invoice per client based on...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    3 years ago

    pls try this

     

    Column = 
VAR _l=maxx(FILTER('Table (2)','Table (2)'[client]=EARLIER('Table (2)'[client])&&'Table (2)'[invoice]<EARLIER('Table (2)'[invoice])),'Table (2)'[invoice])
return if(ISBLANK(_l),BLANK(),'Table (2)'[amount]-maxx(FILTER('Table (2)','Table (2)'[client]=EARLIER('Table (2)'[client])&&'Table (2)'[invoice]=_l),'Table (2)'[amount]))

ryan_mayu's avatar
ryan_mayu
Super User
3 years ago

could you pls provide the expected output?

Anonymous's avatar
Anonymous
Not applicable
3 years ago

hi ryan_mayu ,

it comes down to this:

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    pls try this

     

    Column = 
VAR _l=maxx(FILTER('Table (2)','Table (2)'[client]=EARLIER('Table (2)'[client])&&'Table (2)'[invoice]<EARLIER('Table (2)'[invoice])),'Table (2)'[invoice])
return if(ISBLANK(_l),BLANK(),'Table (2)'[amount]-maxx(FILTER('Table (2)','Table (2)'[client]=EARLIER('Table (2)'[client])&&'Table (2)'[invoice]=_l),'Table (2)'[amount]))

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      ryan_mayu 

      Thank you! 
      Almost works 100%. Only the last month of each years sums all the rows as you can see in the picture. Is there a way to fix this?

       

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        yes , because you have more than one value for that month, what's the expected otuput?