Forcing month start date for fortnight on fortnight comparisons

[#1]

Hi,

I am creating a chart to show:

- the last 14 days of a value (from APStart to APEnd variables)

- the same 14 days a month before (from LMAPStart to LMAPEnd)

- the same 14 days a year before (from LYAPStart to LYAPEnd)

I have calculated measures successfully but am having problems lining them up on the same chart. I would like to use the day of the month as the dimension, but force it to start with the PeriodStart's day of the month. This is so that, if the 14 days overlap with a month end, I don't have a big gap between the two 'populated' sections of the month (plus it being counter intuitive about which is earlier section).

I have tried ticking the expression checkbox in the sort tab and adding $(APStart), Min(TransDate) and a few other combinations, but have not managed to force the start date to start with APStart.

I hope this makes sense and there is a solution out there.

Jonathan

[#2]

Instead of "PeriodStart day of the month", I meant to say "APStart day of the month".

[#3]

In case ayone else has this requirement, I managed to do this as follows:

1. I sett up two extra fields in the calendar:

Calendar:

LOAD

    .....

    Day(TempDate) AS DayLow,

    if(Day(TempDate)<15,Day(TempDate)+16,Day(TempDate)-15) AS DayHigh                            // Start month

    .....

RESIDENT TempDates;

2. I set up a APHighOrLow variable on the front end that detected whether the dates would loop over the end of a month:

     =if(APEarliest<APLatest,'DayLow','DayHigh')

APEarliest was the day of the month for the 14 day period APStart, while APLatest was the day for the end of the analysis period

3. I used this variable as the Sort expression, to reference across to the Calendar sorting field I wanted to use

I hope this helps someone avoid a long day.

Regards,

Jonathan