Annualised Revenue - SUM of revenue for last 12 complete months SSAS mdx calculation -

-

i'm new mdx world , enhancing ssas cube.

i’m trying create calculated member in ssas cube annualised revenue(sum of revenue last 12 complete months) each of device(product). start created below mdx query, showing device names, annualised revenue shows (null).

any correction needed mdx query? or appreciate, if can give me example based on adventureworks cube.

here mdx query:

with    member [measures].[annualised revenue]      sum     (         closingperiod         (           [invoice date].[calendar month].[invoice calendar month]          ,[invoice date].[calendar month].[all periods]         ).lag(12)       :          closingperiod         (           [invoice date].[calendar month].[invoice calendar month]          ,[invoice date].[calendar month].[all periods]         )      ,[measures].[amount]     )  select    [measures].[annualised revenue] on 0  ,[terminal].[terminal id].members on 1 [cube_txn];

do need add cluase “where ( [invoice date].[calendar month].[invoice calendar year].&[2013])”

whenerver runs should pickup current month base , calculate last 12 months revune

i expecting results below(ignore jan14 & jan15):

deviceid    sumofannualisedrevenue  jan14   jan15 ---------   ----------------------  -----    ------ a10001      12,4500     5000   15000 a10003      45,50000    15000   78000 a10006      78,00       12000   890 a10008      8,945       450     120

does help?

with    member [measures].[annualised revenue]      sum     (         closingperiod         (           [invoice date].[calendar month].[invoice calendar month]          ,[invoice date].[calendar month].[all periods]         ).lag(12).item(0).item(0)       :          closingperiod         (           [invoice date].[calendar month].[invoice calendar month]          ,[invoice date].[calendar month].[all periods]         ).item(0).item(0)      ,[measures].[amount]     )  select    [measures].[annualised revenue] on 0  ,[terminal].[terminal id].members on 1 [cube_txn];

if erroring [terminal].[terminal id].members try 1 of following instead (i cannot sure work without seeing db):

[terminal].[terminal id].[terminal id].members  [terminal].members [terminal id].[terminal id].members

Comments

Post a Comment

Popular posts from this blog

node.js - Mongoose: Cast to ObjectId failed for value on newly created object after setting the value -

-
this question has answer here: what's mongoose error cast objectid failed value xxx @ path “_id”? 7 answers even though has been asked many times because entity has no valid objectid field. i came across situation objectid set value can casted objectid still error. the schema: var serviceschema = parammongoose.schema({ servicename:{ type: parammongoose.schema.types.objectid , ref: 'servicenames',required:true } }); pseudo code: servicefromdb=new service({servicename:'some name'}); servicefromdb.servicename='000000000000000000000001'; servicefromdb.save(function(paramerror,paramdata){ if(paramerror){ console.log('but but...',servicefromdb,paramerror) } }); the output of code is: but but... { servicename: 000000000000000000000001, _id: 55079a90286f49280364f78b } { [casterror: cast objectid faile...
Read more

gradle error "Cannot convert the provided notation to a File or URI" -

-
i have defined 2 system properties in gradle.properties: systemprop.builddir=build systemprop.cfgdir=build\\cfg and have following task defined in build.gradle: task clean(group:'clean',description:'clean project') << { ant.sequential { delete(dir: system.properties.builddir) mkdir(dir: system.properties.builddir) delete(dir: system.properties.cfgdir) mkdir(dir: system.properties.cfgdir) } } this generates following error: execution failed task ':clean'. > cannot convert provided notation file or uri: {dir=build}. following types/formats supported: - string or charsequence path, e.g 'src/main/java' or '/usr/include' - string or charsequence uri, e.g 'file:/usr/include' - file instance. - uri or url instance. but equivalent block of code not generate error , works expected: task clean(group:'clean',description:'clean project') << { ...
Read more

python - NameError: name 'subprocess' is not defined -

-
i'm trying make rng website: http://jamesdotcom.com/?p=417 . when run following code: captureimage = subprocess.popen( [ "fswebcam", "-r", "356x292", "-d", "/dev/video0", "static.jpg", "--skip", "10" ], stdout=devnull, stderr=devnull) captureimage.communicate() i following error message: nameerror: name 'subprocess' not defined what should do? thanks have imported first? import subprocess captureimage = subprocess.popen(["fswebcam", ...) subprocess included in python standard library since python 2.4. should not necessary install it.
Read more