Year on year for previous reporting period

[xmark]

Hi all,

I have a data model for a sales company that reports sales by dates are you would normall expect.

But the organisation uses specific reporting "periods" as a fundamental part of their operation, such as Period #1 for FY17/18 is from July 1 - July 30, then Period #2 is July 31st - August 27th (4 weeks), and so on up until the end of the FY (Period 13, ending on June 30th).

The DB model looks like this:

[Period] ---> [DateTable] <-----[SalesItem]

I have many measures that work in this format, so I can create great visualisations based on periods, but I cannot create a measure to compare the Previous year's equivallent Period.

For example, a clustered  column chart with Period #1 (FY16), next to Period #1 (FY 17) for comparison.

I cannot seem to create a measure for this, since the date ranges are DIFFERENT between the years, so cannot do a simple Year-1 calculation which is the normal. I almost need a measure to get the sales date, subtract a year, then "get" the period for that date, and group by that... any ideas? Ideally I would like to avoid a calculated table, since the sales data is very high volume.

Thanks in advance!

Edit:

Here is something I have been playing with, but doesnt work:

ChargeLastYear = CALCULATE([Charge], FILTER(Periods, Periods[Index] - 13))

I added a new index column (starting at 1), since there are always 13 Periods, this formula should calculate the previous period, but this just results in the same value as the current 'Charge' measure. Any ideas? I think im close though...

[v-chuncz-msft · Accepted solution]

@xmark,

You may refer to the DAX below.

ChargeLastYear =
CALCULATE (
    [Charge],
    FILTER ( ALL ( Periods ), Periods[Index] = MAX ( Periods[Index] ) - 13 )
)

[xmark]

hi @v-chuncz-msft that seemed to do the trick! thank you very much.

It looks a little un-intuative to me, so in the interest of learning, could you explain how the MAX part of the formula is applied?

Is it due to the filter context being applied to the calculate function, will mean that the ALL function will sum ALL rows, however, the current row context (which is applied by the iterator used at the time of the visulaisation) will return the current Periods[Index] to the MAX function?

Sorry if that doesnt make sense...