Compare previous period and year where period is a none date

[areadman]

Thanks in advance for help on this.

We use Periods in the format of tax year, month and week.  For example, 1901-01 is:

Tax year: 19 (ie 2019)

Tax month: 01 (ie month one of the tax year 26/03/2018 to 22/04/2018)

Tax week: 01 (ie week one out of 4 weeks in given tax month)

We have a data table of product sales:

Product, Department, Qty, Value, Period

with calculated columns based on Period to extract elements:

Period Yr = LEFT('Weekly Data'[PERIOD], 2)

Period Month = LEFT('Weekly Data'[PERIOD], 4)

Period Week = RIGHT('Weekly Data'[PERIOD], 2)

Example:

Product, Department, Qty, Value, Period, Period Yr, Period Month, Period Week

Becks, Draught, 312, 1060.80, 1901-01, 19, 01, 01

Grey Goose, Vodka, 102, 408, 1901-01, 19, 01, 01

Becks, Draught, 300, 1020, 1901-02, 19, 01, 02

Grey Goose, Vodka, 102, 408, 1901-02, 19, 01, 02

Becks, Draught, 312, 1060.80, 1805-03, 18, 05,03

We are wanting to display in a matrix table for a given selected period, the corresponding previous month and also previous year.

For example:

If we selected, say period 1901-01 to display total Value by product, the corresponding previous's to also show.

ie 1801-01 for previous year, and also 1813-01 for previous month.  (there are 13 tax months in each given tax year).

Rows:

Department

Product

Columns:

Period Month

Period

Values:

Qty

Value

Again, thanks in advance for help on this matter.

[v-danhe-msft]

Hi @areadman,

Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?

Regards,

Daniel He

[areadman]

Hi Daniel

Thanks for the response.  I'm not sure how your solution works, as I want to compare the relative period last year.  

For example if display period 1901, I want to bring up data for 1801 (ie same period, but last year).

Regards

Alex

[areadman]

Hi Daniel

Thanks for the response.  I'm not sure how your solution works, as I want to compare the relative period last year.  

For example if display period 1901, I want to bring up data for 1801 (ie same period, but last year).

Regards

Alex

[v-danhe-msft · Accepted solution]

Hi @areadman,

Based on my test, you could refer to below steps:

Create a new table as slicer:

New Table = DISTINCT('Table1'[ Period Yr])

Create a measure:

Measure = CALCULATE(SUM('Table1'[ Value]),FILTER('Table1','Table1'[ Period Yr]>=SELECTEDVALUE('New Table'[ Period Yr])-1&&'Table1'[ Period Yr]<=SELECTEDVALUE('New Table'[ Period Yr])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He