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
Dear Experts, Thank for reading this. http://maharishihoney.safire.com/ in the above page it is behaving just like wanted, EXCEPT want the top and left side (perhaps) to be 'pinned', meaning when resizing the browser window, the top does not disappea [More]
I created an entity relationship diagram (ERD) using Toad Data Modeler.  I want to save/print it to PDF, which I can just fine.  However, it cuts off tables and is very hard to read.  I'm looking for a format to use that will not cut off the tables.  [More]
Hello, I'm playing with JDeveloper and Java for the first time and am running into some problems. My sample code is importing a bunch of classes, and when I compile my code I'm getting a bunch of errors saying - Error(19,30): cannot access class orac [More]
I use CmsResourceService.retrieveUniverse to retrive *.unx from server to local. Then , I use LocalResourceService to load *.blx and cast to RelationalBusinessLayer and throws exception in my program. The exception is NestedException Interface reques [More]
a link or a button that bind with imagesource. i can see the icon gif files shows in webpage after few days, i check back the the icon show in webpage is gone with 'X'. Is anyone have face this issue before. this is really weird. the project is in DC [More]
I just upgraded to the new 5.0 OS & now can't delete apps..I've read the manual, changed restrictions, cycled the power off & then back on & nothing works...I would just like to be able to delte the unwanted/unused apps that apple includes;  w [More]
I signed up to CC yesterday and installed Premiere. I can open the application, selected an existing project, or create a new one, however, as soon as the project loads Premiere closes down instantly with no error message. I have tried the following: [More]
Iam trying to change my credit card information on my account and it keeps saying my security code is wrong, but I keep typing ti correctly, how so i fix this so i can update my account?This is rediculous. Nobody on these forums are any help. There a [More]
I tried to update my iPhone 4 to the new iOS 7.0.2 but my phone said it needed to restore everything so i tried to do that and now it crashed. The screen is the Itunes screen saying its connected but i held down the home button trying to get it to tu [More]
I can get the internet on my Sony Ericsson W395 but I don't think it is an android phone. So, can I use the BTFON wi-fi service on my mobile somehow and how do I set this up?Hi Stax I don't think this model has built in wifi so you would be unable to [More]