none
Analysis service Tabular Model Rolling the values

    Question

  • Hi

    Help me anyone,

    I Have to calculate for 1 Year rolling values from current date 

    example let say i am in march-2014   i have to rolling my column values for 1 year means April-2013 

    For this what function should i use 

    and also one more thing I have a string values in my column like

    Status

    Active

    Seperated

    Active

    Seperated

    Resigned

    Resigned

    Abscond

    Abscond 

    I need to apply 1 year rolling formula on this column 

    how should i get these total count  for 1 year rolling

    Please help me on that 

    Please reply me soon

    Sreeni


    Sreeni

    Wednesday, March 12, 2014 5:18 AM

All replies

  • Hi Screeni,

    In your scenario, you can to calculate for 1 Year rolling values from current date, right? In MDX, we can calculate it using Lag and ClosingPeriod function, here is a sample query for your reference.

    WITH MEMBER [Measures].[Last 12 Mth Order Count] AS 
    SUM( 
    ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods]).Lag(12)
    : ClosingPeriod([Date].[Calendar].[Month], [Date].[Calendar].[All Periods])
    , [Measures].[Order Count]
    )
    SELECT [Measures].[Last 12 Mth Order Count] ON 0
    FROM [Adventure Works]

    Reference

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, March 13, 2014 3:15 AM
    Moderator
  • Hi thanks for reply ,

    but i need the Formula or function in DAX to write in tabular model not in analysis service MDX formula

    Please if u know share me. 



    Sreeni

    Tuesday, March 18, 2014 10:59 AM