cancel
Showing results for
Did you mean:
Helper I

## Calculate change by department

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Heidi

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

If I resolve your problem Mark it as a solution and give kudos.
4 REPLIES 4
Anonymous
Not applicable

Hi @Heidi

IS TotalSold column or measure?

Thanks,

Pravin

Helper I

Hi @Anonymous

It's a column.

Anonymous
Not applicable

Hi @Heidi

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

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

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

Announcements