Forum Discussion

BryanWhellams's avatar
BryanWhellams
Frequent Visitor
8 years ago

% Changes Using 4 4 5 week Calendar Month

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

 

 

 

 

Need Help

2 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    8 years ago
    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's avatar
      BryanWhellams
      Frequent Visitor
      8 years ago

      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