Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
chamue329
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

@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

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
az38
Community Champion
Community Champion

@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

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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


 

az38
Community Champion
Community Champion

@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

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

 

Running Sales V3.JPG

 

 

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

@chamue329 yes it will show result whatever level you are selected or using in the visual National->State->Customer. Will send you pbix soon and you can take it from there.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@parry2k Appreciate any help with getting it to scale.  I think @az38 has the right approach. I'm assuming it is feasible to mirror the same logic to calculate the % change as well?

@chamue329 % is just another calculation. In attached there are broken down easy to chew measures



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@parry2k Unfortunately I'm unable to open your file.  I am on PBI version 2.78.5740.861 and my organization manages updates.  Is it possible to save to a version compatible with mine?

az38
Community Champion
Community Champion

@chamue329 

replace MAX() to SUM() for running value 

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(SUM('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

@az38 we should be using date table instead of order date as part of best practice, rather trying to make these measures complicated.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@parry2kSuccess! The important thing that to remember was to bring in the "Date" column from the calendar table.  Thank you so much!

@chamue329 woo hoo. happy to hear it is resolved. and I'm sure you know why it works now. Anyhow, bottom line is, solving a problem is one thing but solving in a way using best practice, scalable and easy to manage, and ofcourse performance, is the right way to do this. I always look at problems, how I will solve it for my own work. Although I end up spending lot more time on one issue and some time take shortcuts (oops) but my approach is always to guide the users from my experience.

 

Glad it works out for you. Cheers!!  Would appreciate Kudos 🙂 if my solution helped.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

@chamue329 what is that month?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

My version is from Feb. 2020.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.