Forum Discussion
Value Difference and % Difference Between Consecutive Months
Hi everybody,
I'm trying via DAX to get the following:
1. Average Load per Day in a month for similar group.
2. Value difference of the average load per day in a month between 2 consecutive months for similar group.
3. Percentage difference of the average load per day in a month between 2 consecutive months for similar group.
I'm having a difficult time in creating it since there are also individual dates in my data set. Aside from that, I need to exclude weekends (Saturday and Sunday) in the calculations.
Here is a screenshot of the raw data:
What I want the data to look like for Group A wherein it is in a year/month level:
Avg Load / Day column = Sum of Loads / Sum of Plan Days
Current vs Previous Month Difference = Current Row "Avg Load / Day" - Previous Row "Avg Load / Day"
% Difference = Absolute value((Current vs Previous Month Difference / Previous Row "Avg Load / Day"))*100
Here is the spreadsheet of the raw data:
https://www.dropbox.com/scl/fi/ancdbwdsvn3aehlrbuok0/Data4.xlsx?rlkey=naqgs3c0se2931wg4emsidati&dl=0
Here is the Power BI Desktop wherein raw data is imported:
https://www.dropbox.com/scl/fi/l2dk6lgjxm7p3m4r2hxio/Example.pbix?rlkey=30zlbdiv3210siznebijaafhv&dl=0
Please note that this calculation needs to be done for similar named groups only in column "Group". So for example, Group A will need to get the difference between 2 consecutive year/month level for Group A only. So for Group B, get difference between 2 consecutive year/month level for Group B only. Also weekends (Saturday and Sundays) needs to be excluded in the calculation.
Appreciate your kind assistance.
Best regards,
Mark V.
1 Reply
- markefrodyPost Patron
Hi. Appreciate your help if any. Thanks.