Forum Discussion
No Relations found between two tables
You should combine the data from both of these tables into a single data table either before load (via a query) or during load (via Power Query - Get Data). You should then have a single data table containing date, desc, sales amount.
Then create and load a calendar table (sounds like you may have one of these already). it needs to have the same granularity as the dates in your data table. eg if your data table dates are "day level", then your calendar table must be the same. Read my blog post about calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/
Assuming you have a day calendar table, you can use inbuilt time intelligence to solve the problem.
Total Sales = sum(data[Sales Amount])
Total Sales prior year = calculate([total sales],dateadd(calendar[date],-1,Year))
Thank you for your reply, it was very helpful.
I created the Calendar table und startet to create two measures, with the syntax you posted below.
But the last Syntax seemed to be wrong.
I tried two versions
The first one with the function Date (if you mean that with date)
.... calendar(DATE,-1.Year)).
Or a second version with a reference to my date table
... dateadd(CALENDAR('Date Table'[Date];-1,year))
But everytime Power BI said "wrong syntax for 'year'. (DAX(CALCULATE('XX'[Total Sales],dateadd(CALENDAR('Date Table'[Date],-1.year)))).
So what did i wrong ?
Tobias_Lehmann2 you're using periods where you should be using commas.
Sorry, there was a close square bracket missing in my formula after "date". I have fixed it now