Forum Discussion
variance between columns in a matrix
You would be best served using a data dimension table and creating a second measure for Previous year. The you would use two measure to find the variance. For example in sales you might have:
Total Sales = SUM(Sales[LineSales])
Last year Sales = CALCULATE([Total Sales], DATEADD(DimDate[DateKey], -1, year)
Variance = [Total Sales]-[Last year Sales]
Of course going this route would requre you to drop the year from the columns and list your dates as rows using the measures as values.
Hi kcantor Thank you very much for the guidance.
So far I was not sure how to create a dimension table, but finally I managed to create them,
I started by creating the table, and then assigning a dimension to the year as shown below:
. I started by establishing a values on the following format and also created a timestamp to define a year, as that is the only measure I need.
Can you please confirm how to create the relationship between both tables?
Now, when it comes to create the variance on the main table, I am not sure what sort of code should I follow, I tried yours but I am getting the following errorr on triying to display last year sales
I am creating a Matrix table to show the information, not sure what I am missing here.
Thank you very much!!!
juanmobando you are setting relationship between year and date , both are different data type.
Add a calculated column in your table to create a date and set relation on that column:
- add column DummyDate (or name whatever you want)
DummyDate = Date(Table1[Year], 1,1)
- go to modelling tab and set data type to Date
- set relation on dummydate with your date dimention on date
Hi juanmobando,
Is this case the same with this one of yours? I have replied. Please try it out.
Best Regards,
Dale
