Sitemap

Amicuk Programming Answers

Month vs PY year measure in custom calendar

2015-10-11   Views:0

Advertisement

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 TRADE

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.

The replay answer
Advertisement
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]

Go to See the other 4 answers

Month vs PY year measure in custom calendar

Category:DefaultRelease time:2015-10-11Views:130

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 TRADE[More]

Defining Custom Calendar in W_Day_D

Category:DefaultRelease time:-0001-11-30Views:130

Hi There! We have a requirement to have custom calendar in W_Day_D. The requirement is as follows Year = Calendar year Quarter = Calendar Quarter Month = Calendar Month Week = The week should start from Monday instead of Sunday. The week num should c[More]

How to use custom calendar for scheduling other than owb's default calendar

Category:DefaultRelease time:-0001-11-30Views:130

Hi All, Can anyone please tell me, is there any possibility of using a custom calendar in place of owb default calendar calendar during scheduling. Because, in my case, the month start date and end date are not 1st and 31st that changes according to[More]

After update to IOS 6 on my Ipad2 it not possible to go to month view of March 2013 in the calendar app?

Category:DefaultRelease time:2015-10-11Views:130

after update to IOS 6 on my Ipad2 it not possible to go to month view of March 2013 in the calendar app? on my Ipone with IOS there is no problem is Apple aware of this problem? When will it be solved?Packrat74 wrote: Probably the app developers faul[More]

BOE XI 3.1 Performance Manager/Dashboard Manager - Import Customer Calendar

Category:DefaultRelease time:-0001-11-30Views:130

I am trying to load a custom calendar in Performance Manager through the interface using the Import Calendar. I have followed the format correctly and loaded the CSV file onto BOE. When i try and load the calendar it keeps giving me error message and[More]

How to create custom calendar in webdynpro ABAP.....(WDA)

Category:DefaultRelease time:-0001-11-30Views:130

Hi, How to create custom calendar in Webdynpro Abap ?. Regards, NarasimhaThe best way I see here is to develop your custom calender in any of the UI technologies supported like Flash (flex) or HTML5 (or maybe even SilverLight? not sure) and embed it[More]

Scheduled delivery date according to customer calendar

Category:DefaultRelease time:-0001-11-30Views:130

hi all, i maintained a customer calendar with only friday as work day, and assigned it to customer in uploading point in customer master data. when creating sales order, the system checks the customer calendar with the date proposal from sales order[More]

I am having problems with the month of October.  When I have the full month view, the synced items from google calendars won't show.  But they do for all the other months.

Category:DefaultRelease time:-0001-11-30Views:130

I am having problems with the month of October.  When I have the full month view, the synced items from google calendars won't show.  But they do for all the other months.  There is definitely a glitch somewhere because if I am on day view and try to[More]

Creating local custom calendar type .

Category:DefaultRelease time:-0001-11-30Views:130

Hi all, How to create a custom calendar type .I need to start a job according with local calendar (local holidays)which was not present in SM36.Also Some holiday is not preset in this calendar(cant be predicted in which day it falls ) .I dont want to[More]

Setting Custom Calendar Permissions not working Outlook 2013

Category:DefaultRelease time:-0001-11-30Views:130

Hi, I am trying to create a custom calendar permission for a calendar for a member of staff, he wants another member of staff to see that he's free/busy, create items, edit own and delete own. when I set this, only the free/busy under Read is applied[More]

How to get month value from custom calendar without passing parameter from SSRS in MDX query

Category:DefaultRelease time:2015-10-11Views:130

Could you please throw some light to achieve below requirement?   I need to filter the data between two periods dynamically . The date calendar here works differently(ex:-Date 26-Aug-2014 will fall in period 7 which is last date and 27-Aug-2014 will[More]

Hot
Hi. As I just bought iPad, I tried to use it as a personal hotspot connection for my notebook (windows xp, sp3). In network connection I can see a message: "network cable unppluged" ; apple mobile device ethernet.. that unfortunately means I can [More]
hi folks, I've deployed Collaboration Edge with VCS Control and Expressway: my Jabber for iPhone succesfully can log in from Internet and it can IM but not calls and I see next messages in VCS Expressway when Jabber is logging in: 2015-03-27T18:00:28 [More]
Hi! I have a report where I list financial data about a company. The report is only one pivot table! There is a dashboard prompt, where you can choose the company you want to see. I need to save the report in excel or pdf for ALL the listed companies [More]
Hi I am working in sqlserver 2008 R2 and below is my sample research query i am trying to get previous 6 months data. WITH CutomMonths AS ( SELECT UPPER(convert(VARCHAR(3), datename(month, DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - N, 0)))) Month ,DATE [More]
Hello, Apple replaced the logic board in my laptop last week. Since getting the machine back, a lot of software needed to have its license info re-entered, but ARD is persistently asking for its serial number to be re-entered every time I launch the [More]
Hi, Can you please let me know the Object No of AR Downpayment Request and AR Downpayment Invoice respectively. Regards, Kamlesh     1          oChartOfAccounts      2          oBusinessPartners      3          oBanks      4          oItems      5    [More]
<u>source structure    </u> Message Name         Header(1)                 H1                      H2                  LineItem(1)            Item(1-unbounded)        I1                      I2      <u>target structure</u> Message [More]
After considerable searching on the web, I can't seem to find the product that I am looking for to ease the use of my iPod nano in my car. My recently purchased car has a 3.5 mm jack for connecting my iPod--great, just what I wanted, now I can pass o [More]
Hello I have created a document style webservice, the method signature takes Element as argument and returns elements back. ( i guess this is the limitation of using Document Style Web Service, as I would have rather prefered to have a complex type J [More]
I am having some issues with the erratic nature of menu functions (on your controller- and not a button) when i am in a menu, and use the menu function on either a DVD remote, or the controller in my software, the DVD jumps back to the last place i v [More]