Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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.
Solved! Go to Solution.
@parry2k yes those are the desired results. It just needs to scale to the agg level (State or National) in my visual.
@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
Calendar = CALENDARAUTO()
and mark this new table as data table
set reltionship between date from calendar table with your data table, add following measures
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.
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)))
@az38 I tried your modified measure and still had same results, except now the table with the State and Customer is different:
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:
State | Customer | Date | Running Sales |
CA | A5001 | 3/2/2020 | 10 |
CA | A5001 | 3/3/2020 | 25 |
CA | A5001 | 3/4/2020 | 40 |
CA | A5001 | 3/5/2020 | 97 |
CA | A5001 | 3/6/2020 | 112 |
CA | A5001 | 3/7/2020 | 114 |
CA | A5001 | 3/8/2020 | 119 |
CA | A5001 | 3/9/2020 | 120 |
CA | A5001 | 3/10/2020 | 122 |
CA | A5001 | 3/11/2020 | 124 |
CA | A5001 | 3/12/2020 | 124 |
CA | A5002 | 3/2/2020 | 100 |
CA | A5002 | 3/3/2020 | 120 |
CA | A5002 | 3/4/2020 | 190 |
CA | A5002 | 3/5/2020 | 220 |
CA | A5002 | 3/6/2020 | 230 |
CA | A5002 | 3/7/2020 | 237 |
CA | A5002 | 3/8/2020 | 240 |
CA | A5002 | 3/9/2020 | 250 |
CA | A5002 | 3/10/2020 | 260 |
CA | A5002 | 3/11/2020 | 268 |
CA | A5002 | 3/12/2020 | 269 |
NC | G128 | 3/2/2020 | 878 |
NC | G128 | 3/3/2020 | 890 |
NC | G128 | 3/4/2020 | 904 |
NC | G128 | 3/5/2020 | 928 |
NC | G128 | 3/6/2020 | 991 |
NC | G128 | 3/7/2020 | 1022 |
NC | G128 | 3/8/2020 | 1025 |
NC | G128 | 3/9/2020 | 1027 |
NC | G128 | 3/10/2020 | 1198 |
NC | G128 | 3/11/2020 | 1280 |
NC | G128 | 3/12/2020 | 1300 |
NC | G204 | 3/2/2020 | 740 |
NC | G204 | 3/3/2020 | 750 |
NC | G204 | 3/4/2020 | 777 |
NC | G204 | 3/5/2020 | 789 |
NC | G204 | 3/6/2020 | 799 |
NC | G204 | 3/7/2020 | 813 |
NC | G204 | 3/8/2020 | 850 |
NC | G204 | 3/9/2020 | 873 |
NC | G204 | 3/10/2020 | 902 |
NC | G204 | 3/11/2020 | 928 |
NC | G204 | 3/12/2020 | 987 |
Thank you.
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))
@az38 it returns properly, but when I remove Customer it still throws things off (values become negative).
@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
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?
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))
@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
Calendar = CALENDARAUTO()
and mark this new table as data table
set reltionship between date from calendar table with your data table, add following measures
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |