% Changes Using 4 4 5 week Calendar Month

[BryanWhellams]

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

[Eric_Zhang]

---

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

[BryanWhellams]

Thanks for this

The data table is called DSS Data Cleaned, the dates table is called YMWDate.

I have already got columns in the Dates table for Week No, Date of First Day Of Week, etc.

I have created a measure for Appointments Current Week as follows:

Appointments Current Week = SUM('DSS Data Cleaned'[Appointment])

I have also created a measure for Appointments Previous Week as follows:

Appointment Previous Week = SUMX (
    FILTER (
        ALLSELECTED ('DSS Data Cleaned'),
        'DSS Data Cleaned'[WeekNo]
            = MAX ('DSS Data Cleaned'[WeekNo]) - 1
    ),
    SUM('DSS Data Cleaned'[Appointment])
)

but it is returning figures which are way too high.

Could you tell me where I might be going wrong please?

Thanks