# Month vs PY year measure in custom calendar

• ### Question

• Dear All

I have go two RELATED tables in my Powerpivot data model:

 Table: PTV FLAT HITLIST COUNTRY BRAND TV MODEL INCH SIZE SALES THS#EUR PERIOD HUNGARY PANASONIC TX-L32C20 32 100 2012 JUN HUNGARY SHARP LC-32SH7EBK 32 101 2012 JUN HUNGARY TRADEBRAND TRADE MODEL 32 102 2012 JUN POLAND FUNAI LH840-M32 32 103 2012 JUN POLAND FUNAI LT6-M32 32 104 2012 JUN ...

Table: PERIOD

 PERIOD PERIOD NUMBER MONTH NUMBER DATE FISCAL QUATER FIRST PERIOD IN YEAR FISCAL YEAR 2012 JAN 01 1 2012-01 Q4 1 2011 2012 FEB 02 2 2012-02 Q4 1 2011 2012 MAR 03 3 2012-03 Q4 1 2011 2012 APR 04 4 2012-04 Q1 4 2012 2012 MAY 05 5 2012-05 Q1 4 2012 2012 JUN 06 6 2012-06 Q1 4 2012 2012 JUL 07 7 2012-07 Q2 4 2012 2012 AUG 08 8 2012-08 Q2 4 2012 2012 SEP 09 9 2012-09 Q2 4 2012 2012 OCT 10 10 2012-10 Q3 4 2012 2012 NOV 11 11 2012-11 Q3 4 2012 2012 DEC 12 12 2012-12 Q3 4 2012 2013 JAN 13 1 2013-01 Q4 4 2012 2013 FEB 14 2 2013-02 Q4 4 2012 2013 MAR 15 3 2013-03 Q4 4 2012 2013 APR 16 4 2013-04 Q1 16 2013 2013 MAY 17 5 2013-05 Q1 16 2013 2013 JUN 18 6 2013-06 Q1 16 2013

...

First one is quite big so I present only part. There are more columns with sales facts and product's features which was hidden to keep it more clear.

What I need is to have a measure which describes a month other month sales. So for example:

JAN 2014        JAN 2013    DIFF VS PY

200 eur           150 EUR      1,333

So the expected result is 1,33

I wrote a measure:

DIFF VS PY:=CALCULATE(SUM([SALES THS#EUR]),FILTER(PERIOD,MAX(PERIOD[FISCAL YEAR])))/CALCULATE(SUM([SALES THS#EUR]),ALL(PERIOD[FISCAL YEAR]),FILTER(ALL(PERIOD[FISCAL YEAR]),PERIOD[FISCAL YEAR]=MAX(PERIOD[FISCAL YEAR])-1))-1

which works on Total, but not months. On a pivot table it looks like this:

 Row Labels SALES THS EUR DIFF VS PY 2011 454,777 2012-01 190,961 2012-02 136,252 2012-03 127,564 2012 2,071,222 355% 2012-04 121,442 2012-05 148,521 2012-06 139,187 2012-07 144,297 2012-08 132,714 2012-09 142,333 2012-10 194,294 2012-11 232,935 2012-12 339,444 2013-01 184,280 2013-02 138,139 2013-03 153,636 2013 2,002,603 -3% 2013-04 131,322 2013-05 157,068 2013-06 124,342 2013-07 132,663 2013-08 124,874 2013-09 142,968 2013-10 178,709 2013-11 227,422 2013-12 324,105 2014-01 195,916 2014-02 139,371 2014-03 123,843 2014 765,310 -62%

How to correct the measure to work on months too?

I have tried this but not working (returns blanks):

Copy of DIFF VS PY:=CALCULATE(SUM([SALES THS#EUR]),FILTER(PERIOD,MAX(PERIOD[PERIOD NUMBER])))/CALCULATE(SUM([SALES THS#EUR]),ALL(PERIOD[PERIOD NUMBER]),FILTER(ALL(PERIOD[PERIOD NUMBER]),PERIOD[PERIOD NUMBER]=MAX(PERIOD[PERIOD NUMBER])-12))-12

G.

Thursday, November 13, 2014 11:44 AM

• Try this:

```Sales:=
SUM( 'PTV FLAT HITLIST'[SALES THS#EUR] )

PY Sales:=
CALCULATE(
[Sales]
, FILTER( ALL( PERIOD[FISCAL YEAR] )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
)
)

Sales vs PY Sales:=
[Sales] / [PY Sales]```

This will work if you put PERIOD[MONTH NUMBER] on the rows under PERIOD[FISCAL YEAR].

If you must keep PERIOD[DATE] on the rows, then you'll need to handle this a little differently. PERIOD[DATE]  uniquely identifies a month. This means that with the measures I have suggested above, the filter context will not be cleared from PERIOD[DATE] and so the filter context of CALCULATE() will be an impossible condition:

On Row Label 2014-03, the filter context will be PERIOD[DATE] = 2014-03 && PERIOD[FISCAL YEAR] = 2013.

If you must keep PERIOD[DATE] as your rowfilter, try this:

```Sales:=
SUM( 'PTV FLAT HITLIST'[SALES THS#EUR] )

PY Yr Sales:=
CALCULATE( [Sales]
, FILTER( ALL( PERIOD[FISCAL YEAR] )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
)
)

PY Mo Sales:=
CALCULATE( [Sales]
, FILTER( ALL( PERIOD )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
&& PERIOD[MONTH NUMBER] = MAX( PERIOD[MONTH NUMBER] )
)
)

PY Sales:=
IF( ISFILTERED( PERIOD[DATE] )
, [PY Mo Sales]
, [PY Yr Sales]
)

Sales vs PY Sales:=
[Sales] / [PY Sales]```

• Proposed as answer by Thursday, November 13, 2014 5:07 PM
• Marked as answer by Friday, November 14, 2014 11:26 AM
Thursday, November 13, 2014 5:03 PM

### All replies

• Try this:

```Sales:=
SUM( 'PTV FLAT HITLIST'[SALES THS#EUR] )

PY Sales:=
CALCULATE(
[Sales]
, FILTER( ALL( PERIOD[FISCAL YEAR] )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
)
)

Sales vs PY Sales:=
[Sales] / [PY Sales]```

This will work if you put PERIOD[MONTH NUMBER] on the rows under PERIOD[FISCAL YEAR].

If you must keep PERIOD[DATE] on the rows, then you'll need to handle this a little differently. PERIOD[DATE]  uniquely identifies a month. This means that with the measures I have suggested above, the filter context will not be cleared from PERIOD[DATE] and so the filter context of CALCULATE() will be an impossible condition:

On Row Label 2014-03, the filter context will be PERIOD[DATE] = 2014-03 && PERIOD[FISCAL YEAR] = 2013.

If you must keep PERIOD[DATE] as your rowfilter, try this:

```Sales:=
SUM( 'PTV FLAT HITLIST'[SALES THS#EUR] )

PY Yr Sales:=
CALCULATE( [Sales]
, FILTER( ALL( PERIOD[FISCAL YEAR] )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
)
)

PY Mo Sales:=
CALCULATE( [Sales]
, FILTER( ALL( PERIOD )
, PERIOD[FISCAL YEAR] = MAX( PERIOD[FISCAL YEAR] ) - 1
&& PERIOD[MONTH NUMBER] = MAX( PERIOD[MONTH NUMBER] )
)
)

PY Sales:=
IF( ISFILTERED( PERIOD[DATE] )
, [PY Mo Sales]
, [PY Yr Sales]
)

Sales vs PY Sales:=
[Sales] / [PY Sales]```

• Proposed as answer by Thursday, November 13, 2014 5:07 PM
• Marked as answer by Friday, November 14, 2014 11:26 AM
Thursday, November 13, 2014 5:03 PM
• I tried Greg2178 approach in similar situation and received just filtered sales for one period. I mean there are MAT TY, MAT LY, MAT 2LY and related to them ID in separate table: 1, 2 and 3. And when I applied proposed formula I saw just MAT LY column filled with MAT LY sales. Other columns in pivot table were empty.

I completely confused with this. Please, help me with advice why formula works in wrong way. I've searched via web and found that proposed formula is the common solution for custom date.

Friday, November 14, 2014 9:03 AM
• Thanks Greg

I have already tested first option you had proposed. It's enough and it is working.

Di_No

This scenario do not focus on MAT periods. Its compare only for example JAN 2014 to JAN 2013.

But you can open new topic with your question. It will be useful for others when you get a solution

G.

Friday, November 14, 2014 11:39 AM