Calculate change by department

[Anonymous]

Hi, 

DAX-newbie here. I'm losing my mind with this problem, so time to ask help! 

I have a data source that has dates, departments and for each date the sum of total value of all transactions. If on Monday the daily transactions are 50 and on Tuesday 30, then in the table Mon is 50 and Tue 80. 

What I want to achieve is the daily sales for each department. I'm sure this is easy to achive, but I'm just not getting it. I keep getting stuck on trying to filter by department, and it being impossible because text cannot be used in boolean experssion. I imagine this is DAX101-stuff, so the solution I imagine is simple

Could someone help out?

DATE	DEPARTMENT	TOTALSOLD	DAILYTRANSACTIONS (THIS IS WHAT I WANT)
1/1/2020	Sports	100	100
1/1/2020	Clothing	50	50
1/1/2020	Food	70	70
1/2/2020	Sports	150	50
1/2/2020	Clothing	80	30
1/2/2020	Food	90	20
1/3/2020	Sports	170	20
1/3/2020	Clothing	120	40
1/3/2020	Food	110	30

[Anonymous]

Hi @Anonymous 

IS TotalSold column or measure?

Thanks,

Pravin

[Anonymous]

Hi @Anonymous 

It's a column. 

[Anonymous · Accepted solution]

Hi @Anonymous 

Create rank column =

Rank = RANKX(FILTER(Sheet3,Sheet3[DATE]<EARLIER(Sheet3[DATE]) && Sheet3[DEPARTMENT]=EARLIER(Sheet3[DEPARTMENT])),Sheet3[DATE],,ASC)

Then create one more column

current sale = Sheet3[TOTALSOLD]-CALCULATE(MAX(Sheet3[TOTALSOLD]),FILTER(Sheet3,Sheet3[DEPARTMENT]=EARLIER(Sheet3[DEPARTMENT]) && Sheet3[Rank]<EARLIER(Sheet3[Rank])))

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

That worked perfectly, thank you so much, @Anonymous !