Forum Discussion

RekhaG's avatar
RekhaG
Frequent Visitor
10 years ago

How to create a Year on Year Measure in Desktop

How do I create a year on year comparison measure in Desktop? My source is a SSAS Tabular cube.   Thanks, Rekha
Need Help
jcox's avatar
jcox
Icon for Advocate II rankAdvocate II
10 years ago

Creating a measure in Custom Columns in Query Editor:

 

= if[Date] < Date.StartOfDay(DateTime.LocalNow()) then [Value] else null

 

Creating a YTD Flag in Custom Columns in Query Editor:

 

= if[Date] < Date.StartOfDay(DateTime.LocalNow()) then "Y" else "N"

 

Let me know if this is something that works or if you require a DAX measure.

  • RekhaG's avatar
    RekhaG
    Frequent Visitor
    10 years ago

    Thanks for the response jcox.

     

    Below are more details of  my request.

     

    My TimeDimension looks like this

     

    Year ( values 2016, 2015,..)

    Month (values 2016-07, 2016-06,....)

    Month End Date (Date Type - values 7/31/2016, 6/30/2016,....)

     

    The measure defined in the cube is

    [Claim Count]

     

    I want the following measure to be defined in the Desktop, so that I neednt create the new measure in the cube

     

    ClaimCount_LastYear : which would be ClaimCount of the same month in the previous year

    ClaimCount_Change : which is difference between ClaimCount of this year and ClaimCount of the same month in last year

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi RekhaG,

       

      According to your description, you want to get [Claim Count],[ClaimCount_LastYear],[ClaimCount_Change],right?

       

      You could follow below steps:

       

      1. Create a TimeDimension table.

       

      TimeDimension = SELECTCOLUMNS( CALENDAR(DATE(2015,1,1),date(2016,12,31)),"Year",YEAR([Date]),"Month",FORMAT([Date],"mm/yyyy"),"Month End Date",FORMAT([Date],"dd/MM/yyyy"))

       

       

      2. Add measures and put them to columns.

       

      Measures:

      Claim Count = CALCULATE(COUNTA(TimeDimension[Month]),FILTER(ALL(TimeDimension),COUNTAX(FILTER(TimeDimension,TimeDimension[Month]=EARLIER(TimeDimension[Month])),TimeDimension[Month])))

       

      PreviousYear = var temp=  DATEVALUE(VALUES(TimeDimension[Month]))

      return

      FORMAT(date(YEAR(temp)-1,MONTH(temp),DAY(temp)),"mm/yyyy")

       

      Columns:

      ClaimCount = [Claim Count]

      Previous Year = [PreviousYear]

       

       

      3. Add new table to store these records.

       

      Result = ADDCOLUMNS( ADDCOLUMNS(TimeDimension,"ClaimCount_PerviousYear",LOOKUPVALUE(TimeDimension[ClaimCount],TimeDimension[Month],TimeDimension[Previous Year])),"Difference",if([ClaimCount_PerviousYear]<>0,ABS([ClaimCount_PerviousYear]-[ClaimCount]),BLANK()))

       

       

       

      Regards,

      Xiaoxin Sheng