% Changes Using 4 4 5 week Calendar Month

---

BryanWhellams wrote:

Hi there

 

I need to be able to calculate the % Changes in a number of measures using the 4 4 5 Calendar Month.

 

I have two tables  as follows:

 

One called AppointmentData which includes Appointment Dates and Appointments (either a 1 or a 0).

 

Another called YMW Date that includes the Week Number, Quarter Number, Month Number and Month Name for the 4 4 5 week calendar month, ie. 2nd Jan 2017 is week 1701, 9th Jan is week 1702 and so on, 2nd to 29th Jan is Month 1, 30th Jan is Month 2 and so on in 4 4 5 week month patterns.

 

I need to be able to get % Changes for Week on Week, Month on Month and Quarter on Quarter using the dates in the 4 4 5 week calendar month.

 

I'm finding this really challenging so any help greatly appreciated.

 

Thanks

Bryan

 

 

 

 

---

BryanWhellams

Since you've already have that 4-4-5 calendar, you can create individual index column for Week, Month and Quarter. eg, for Week

WeekIndex = RANKX('calendar','calendar'[Week Number],,ASC,Dense)

Then create measures as below.

current Week Appnmts = SUM(AppointmentData[Appointments])


previous Week Appnmts =
SUMX (
    FILTER (
        ALLSELECTED ( AppointmentData ),
        Calendar[WeekIndex]
            = MAX ( Calendar[WeekIndex] ) - 1
    ),
    AppointmentData[Appointments]
)

% change = DIVEDE([current Week Appnmts]-[previous Week Appnmts],[previous Week Appnmts])

Then put the column [Week Number](1702,1703 etc) as a X-Axis and the [% change] as value field.