Forum Discussion
YOY Comparison
Hi,
I have seen several conversation surrounding this topic but I can't seem to find a solution that works for the data set I am working with. Although new to Power BI, the DAX experessions are easy to follow but the main difference I am seeing is that other data sets are including daily data whereas I only have month and year. I feel like it shouldn't make a difference but I am still having issues find the solution. Any tips? Please see columns headers below. We can use revenue for this exercise. Any help is much appreciated.
8 Replies
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?
