YOY Comparison

jmstran87 · ‎05-03-2019

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.

Anonymous · ‎05-03-2019

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.

jmstran87 · ‎05-06-2019

@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?

Anonymous · ‎05-07-2019

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.

jmstran87 · ‎05-07-2019

@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

jmstran87 · ‎05-08-2019

Ok. That did not work. If someone can provide step by step what is needed to be done in order to generate YoY, given the tables I have, that would be great. I guess DAX isn't as easy as I thought. Note that data in the Ecom table is only provided at a Monthly level.

DimDate Table

Ecom Table

TeigeGao · ‎05-14-2019

Hi @jmstran87 ,

It would be pleasant if you can share the pbix file (with sample data) to us, we need to use it for test and analysis what has caused the problem.

Best Regards,

Teige

jmstran87 · ‎05-15-2019

@TeigeGao

This is gonna sound absurd but how do I inlclude the pbix file? Can I not just drag and drop the file into the message window here? There isn't an attach file option either.