cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Daily change rate for various aggregation level (ie. customer by state or national)

Hi,

I have a table with running sales by state by customer by date iterating down.  I received some help with DAX measures to calculate the daily change and % change, however, the results are negative values when I try to aggregate up to state or even national.  Here's my table sample:

 

Running Sales.JPG

Here is the fantastic DAX syntax for daily change and daily change % that @az38 constructed :

 

Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]<SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
SELECTEDVALUE('Table'[Running Sales])-CALCULATE(MAX('Table'[Running Sales]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]=_prevDate)))
% Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]<SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
DIVIDE(SELECTEDVALUE('Table'[Running Sales]), CALCULATE(MAX('Table'[Running Sales]),FILTER(ALL('Table'),'Table'[Customer]=SELECTEDVALUE('Table'[Customer]) && 'Table'[Date]=_prevDate))) - 1)

 

As you can see in the table example, when I remove the customer and display results by state, I get negative values.  Anyone with ideas? I've spent the past day trying to figure this out.  Thanks.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper II
Helper II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@parry2k yes those are the desired results. It just needs to scale to the agg level (State or National) in my visual.

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 I don't have older version but here are the steps,

 

ADd a calendar table in your model, called it Calendar and you can use following DAX expression to create one

 

image.png

Calendar = CALENDARAUTO()

 

and mark this new table as data table

 

image.png

 

set reltionship between date from calendar table with your data table, add following measures

 

image.png

 

Add following measures

 

RT Sum = SUM ( 'Table'[Running Sales] )

RT Sum Prev Day = CALCULATE ( [RT Sum], DATEADD ( 'Calendar'[Date], -1, DAY ) ) 

RT Diff = [RT Sum] - [RT Sum Prev Day]

 

on table visual, drop date from calendar table (very important) and then rest of the columns from your data table (state, customer etc) and above measures and you should be good to go.

 

Would appreciate Kudos 🙂 if my solution helped.

 

 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

21 REPLIES 21
Highlighted
Super User IV
Super User IV

Re: Daily change rate for various aggregation level (ie. customer by state or national)

Try Measure with a date calendar

Current  = SUM(Sales[Running Sales]) 
//Or
Current  = SUM(Sales[Running Sales], filter('Date','Date'[Date]=max('Date'[Date]))

Day behind Sales = CALCULATE(SUM(Sales[Running Amount]),dateadd('Date'[Date],-1,Day))
//OR
Day behind Sales = SUM(Sales[Running Sales], filter('Date','Date'[Date]=max('Date'[Date])-1))


diff  =[Current] =Day behind Sales

 

 

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/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User II
Super User II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 

its because your customer is getting blank as you drop it from visual

from more complicated condition try to use

Daily Chg = 
var _prevDate = CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'), 'Table'[Date]<SELECTEDVALUE('Table'[Date])))
RETURN
IF(ISBLANK(_prevDate), BLANK(),
SELECTEDVALUE('Table'[Running Sales])-CALCULATE(MAX('Table'[Running Sales]),FILTER(ALLSELECTED('Table'), 'Table'[Date]=_prevDate)))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Helper II
Helper II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@az38 I tried your modified measure and still had same results, except now the table with the State and Customer is different:

 

Running Sales Ver 2.JPG

As another member recommended, can adding separate date table help this situation? Thanks.

Highlighted
Super User IV
Super User IV

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 You are in safe hands of other awesome leader who are helping you but here is my 2cent?

 

Do you have running sales as column or it is a measure? if it is a measure, share the expression, if it is a column then just ignore it.

Do you want change difference between same customer for previous day? Correct?

 

Can you past the sample data here so that someone can work on data and give you actual solution. When you are working with the dates, make sure you have calendar table in the model and work with that. It is a best practice. I don't just like to solve the problem but reather guide what is the best practice and how you make sure you have sclable solution. 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@parry2k I have running sales as a column.  Yes - I wish to show change from previous day.  So far in working with @az38 , he has given me an effective solution that works as long as I have Customer in my visuals and I have not had to rely on a separate date table yet.

 

Here is my sample data:

 

StateCustomerDateRunning Sales
CAA50013/2/202010
CAA50013/3/202025
CAA50013/4/202040
CAA50013/5/202097
CAA50013/6/2020112
CAA50013/7/2020114
CAA50013/8/2020119
CAA50013/9/2020120
CAA50013/10/2020122
CAA50013/11/2020124
CAA50013/12/2020124
CAA50023/2/2020100
CAA50023/3/2020120
CAA50023/4/2020190
CAA50023/5/2020220
CAA50023/6/2020230
CAA50023/7/2020237
CAA50023/8/2020240
CAA50023/9/2020250
CAA50023/10/2020260
CAA50023/11/2020268
CAA50023/12/2020269
NCG1283/2/2020878
NCG1283/3/2020890
NCG1283/4/2020904
NCG1283/5/2020928
NCG1283/6/2020991
NCG1283/7/20201022
NCG1283/8/20201025
NCG1283/9/20201027
NCG1283/10/20201198
NCG1283/11/20201280
NCG1283/12/20201300
NCG2043/2/2020740
NCG2043/3/2020750
NCG2043/4/2020777
NCG2043/5/2020789
NCG2043/6/2020799
NCG2043/7/2020813
NCG2043/8/2020850
NCG2043/9/2020873
NCG2043/10/2020902
NCG2043/11/2020928
NCG2043/12/2020987

 

Thank you.


 

Highlighted
Super User IV
Super User IV

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 you don't have to have customer in the visual if it is not required. Anyhow, jumping on a meeting (2 hours long) and will get to it after that. If someone else helped before that, awesome.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User II
Super User II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 

try

Daily Chg = 
var _curDate = MAX(Orders[Date])
var _prevDate = CALCULATE(MAX('Orders'[Date]), ALLSELECTED(Orders[Running Sales]),'Orders'[Date] < _curDate)
RETURN

IF(ISBLANK(_prevDate), BLANK(),
SELECTEDVALUE('Orders'[Running Sales])-CALCULATE(MAX('Orders'[Running Sales]), ALLSELECTED(Orders[Running Sales]), 'Orders'[Date]=_prevDate))

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Highlighted
Super User IV
Super User IV

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@chamue329 is this the result you are expecting? Showing both with and without customer

 

image.png






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Daily change rate for various aggregation level (ie. customer by state or national)

@az38 it returns properly, but when I remove Customer it still throws things off (values become negative).

 

Running Sales V3.JPG

 

 

Helpful resources

Announcements
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.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors