cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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?

DATEDEPARTMENTTOTALSOLDDAILYTRANSACTIONS (THIS IS WHAT I WANT)
1/1/2020Sports100100
1/1/2020Clothing5050
1/1/2020Food7070
1/2/2020Sports15050
1/2/2020Clothing8030
1/2/2020Food9020
1/3/2020Sports17020
1/3/2020Clothing12040
1/3/2020Food11030

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Calculate change by department

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
www.linkedin.com/in/pravin-p-wattamwar

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

View solution in original post

4 REPLIES 4
Highlighted
Anonymous
Not applicable

Re: Calculate change by department

Hi @Heidi 

 

IS TotalSold column or measure?

 

Thanks,

Pravin

Highlighted
Helper I
Helper I

Re: Calculate change by department

Hi @Anonymous 

 

It's a column. 

Highlighted
Anonymous
Not applicable

Re: Calculate change by department

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
www.linkedin.com/in/pravin-p-wattamwar

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

View solution in original post

Highlighted
Helper I
Helper I

Re: Calculate change by department

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors