Forum Discussion

markefrody's avatar
markefrody
Post Patron
3 years ago

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