Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
How do I create a year on year comparison measure in Desktop?
My source is a SSAS Tabular cube.
Thanks,
Rekha
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.
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.
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
User | Count |
---|---|
111 | |
105 | |
101 | |
77 | |
44 |
User | Count |
---|---|
144 | |
142 | |
122 | |
100 | |
56 |