How to create a Year on Year Measure in Desktop

[RekhaG]

How do I create a year on year comparison measure in Desktop?

My source is a SSAS Tabular cube.

Thanks,

Rekha

[jcox]

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]

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.

[v-shex-msft]

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