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
while in firefox 6.0 i cannot open pdf files with adobe reader x. how do configure firefox correctly as i have no problem with internet explorer opening pdf files? os is win7 ultimate.See [https://support.mozilla.com/en-US/kb/Opening%20PDF%20files%20 [More]
I'm facing some new issue using HP laserjet M1536dnf printer & Scanner, in my office number of systems are there, i can able to give print and scan from all systems accept one system i tried so many settings and troubleshoot's but no use its not work [More]
I am a long time ASP developer and looking to get into HTML5 using Dreamweaver CS5.5 for Mobile apps. I am very excited to understand the mobile development features of CS5.5! I'm hoping somebody can point me into the right direction or explain what [More]
Hi All, Could any one help how to fill SoapAction parameter in XML file generated for webservice in sap. <soap:operation soapAction="" style="document" /> Thanks & regards , harshaHI, I think you are not created wsdl url with [More]
When pages give me a check box that says "save copy as ms word doc" is it lying? It looks like it doesn't copy anything, it just saves the file as a word doc when I select that option. So, the real question is "do I look around more for the [More]
I try to install a DI on an MSCS cluster/SQL. On the phase called "unpack SAP archive",  anyway SAPinst cannot move forward. The sapinst_dev.log reads: (many repeated  loops as follows) TRACE      [iaxxejsexp.cpp:199]            EJS_Installer::w [More]
We are looking to build a 2-node 10g RAC cluster on Solaris 10. We will have two databases in this cluster, in addition to the ASM instance. We want to provide HA for this setup using IPMP. My understanding is that with Solaris/IPMP, CRS will not be [More]
Hello, I'm having some issues with some users because when they try to sign on into Cisco Jabber or Webex Communicator the next error message is shown "User account has been deactivated". I was checking their Active Directory account and have no [More]
plz tell me how to do this....    thanks in advanceJust wire it. Your (poor) question speaks volumes about your lack of experience. Have you taken the basic tutorial?Read other 2 answers [More]
I want to buy some 5.1 speakers and use them for my macbook. Can this be done or will only 2 speakers work?Hi brad You can use 5.1 speakers, but you have to choose your purchases very carefully. Here are your options: A. If you ONLY ever want to watc [More]