locked
Month vs PY year measure in custom calendar RRS feed

  • 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

Answers

  • 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 Michael Amadi Thursday, November 13, 2014 5:07 PM
    • Marked as answer by Bartek Wachocki 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 Michael Amadi Thursday, November 13, 2014 5:07 PM
    • Marked as answer by Bartek Wachocki 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.

    Thanks in advance!

    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