Subtract Previous Date Sales from Current Date Sales and show result in a new column

[Anonymous]

So I have been working on the mentioned subject for past 10 hours straight and I give up. Need your help guys.

I have a table which has data as first table below.

I want a column that subtracts the Current date total sales for each member from previous date and shows the result as 'Today's Sale' for that member. 

Date	Sales Man	Total Sales
04/01/2020	A	2
04/01/2020	B	4
04/01/2020	C	6
04/02/2020	A	5
04/03/2020	B	6
04/03/2020	C	10

Date	Sales Man	Total Sales	Today's Sale
04/01/2020	A	2	2
04/01/2020	B	4	4
04/01/2020	C	6	6
04/02/2020	A	5	3
04/03/2020	B	6	2
04/03/2020	C	10	4

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

1.Go to query editor> add column> add index column;

2.Create a calculated column to rank the sales man column grouped by sales man:

Column = 
RANKX(FILTER('Table','Table'[Sales Man]=EARLIER('Table'[Sales Man])),'Table'[Index],,ASC,Dense)

3.Create a measure as below:

Measure = 
var a=CALCULATE(MAX('Table'[Total Sales]),FILTER(ALLSELECTED('Table'),'Table'[Column]=SELECTEDVALUE('Table'[Column])-1&&'Table'[Sales Man]=SELECTEDVALUE('Table'[Sales Man])))+0
Return
MAX('Table'[Total Sales])-a

Finally you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Anonymous , The above solution from @parry2k will work. You can also try trailing day measure

Diff = SUM ( Table[Sales] ) -CALCULATE (SUM(Table[Sales]), dateadd('Date'[Date],-1,day ))

As mentioned by Parry, To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[parry2k]

@Anonymous try following measure, key here is to add a calendar dimension in your model, set relationship with your sales table with this dimension and mark this dimension as date table. This will make any time intelligence calculation super easy and it is also a best practice.

Prev Day Sales Diff = 
SUM ( TableSales[Total Sales] ) - 
CALCULATE ( SUM ( TableSales[Total Sales] ), PREVIOUSDAY( 'Calendar'[Date] ) ) 

Would appreciate Kudos 🙂 if my solution helped.