Forum Discussion
YOY Comparison
DAX has several DATE functions like SAMEPERIODLASTYEAR, YTD, MTD, QTD etc... and it is always a good practice to maintain a date table (something like calendar with Datekey, Month, Year, Quarter, FiscalMonth etc...) and link your date field from transaction data to the DateKey of the date table (like Calendar).
I would suggest you add a calculated table in your data model using CALENDAR() function and add a calculated column for the date in your transaction data while maintaining the granularity at a monthly level. Something like
TransactionDate = DATE(TransactionTable[Year],TransactionTable[Month],1)
will do.
This way your calculations over date will become much easier and the DAX engine will handle it better with inbuilt DAX functions. There is no reason for you avoid the standard functions and do some workaround methods for this.
Anonymous
Thanks for the feedback. I've created a dimdate table and tried to create a 'Revenue LY' measure. When I pull it into the workspace, I just get an error.
Relationship below
Can you help me understand what I'm doing wrong?
You are getting that warning because you are creating a relationship between month fields of both the table. Instead, I suggest you add a date field to your ecommerce table and create a relationship between the date fields instead of month fields.
Anonymous
The reason that there is only a month field in my ecommerce table is because I only receive data on a monthly basis. Would creating a date field to still represent the month make a difference? I'll go ahead and try it out.
- James
