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

3 Replies

  • 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