Year on Year percentage variance

[Anonymous]

Hello,

I was hoping you could help. I have a CSV file in Power Bi which includes multiple years of date (not the ideal method of showing data but current interim).

I have created a Matrix model with each year on the top and value by platform

Yr1 Yr2 Yr3 Yr4
1
2
3
4


Years 1, 2 and 3 are full years of data whereas Year 4 is partial and will be updated monthly to create a full year

I want to work out the % difference between years 2 and 3 in the Matrix with a formula that works calculates the % difference over the last 2 FULL years of data. A friend and I have been trying for ages and can't get anywhere.

My full years run from March 15th-March 15th

Thank you!

[Anonymous]

Hi.

Well, maybe you just can't get anywhere because instead of creating a proper data model you are trying to cut corners with a model that sucks? This often happens... A good model must have a proper data table that is designated as such (there is an option on the ribbon that lets you mark a table as date table). Please, first build a correct model and then make a screenshot of the relationships. Then make a screenshot of the matrix with all the slicers and what have you... and then, and only then, I could help you. The more info about your model you include, the better the chance that someone will actually find an answer to your question.

Thanks!

Best

Darek

[Anonymous]

Hello,
Thanks for your reply. I am very new to all of this and have no formal training. This was thrown at me to solve and I'm learning as I go.

We are trying to create this with a different data model:

- upload individually and then link (we struggle to make a relationship)
- put everything into a folder, link this and combine the files

Would you agree these would work?

Thanks

[Anonymous · Accepted solution]

There are many good courses on data modeling in Power BI. Here's one of them (I have no affiliation to PluralSight, I just sometimes use their services):

PluralSight Course on Data Modeling in Power BI

There is also a course on data modeling (and DAX) at www.sqlbi.com. Again, I'm not affiliated with them but have gone through their courses.

Basics are these:

1. You should always aim for the STAR SCHEMA, that is, your fact table should be surrounded with dimensions that join to your fact table in a 1-to-many fashion. Your fact table should only store metrics (numbers) and keys to your dimensions. It's best to hide all the columns of your fact table so that only measures defined in it are visible to the end user and slicing is done by selecting attributes from dimensions. Fact tables should not store row identifiers but dimensions must.

2. You should always have proper Date Tables.

3. You should never join 2 fact tables directly. They can and should be joined via dimensions.

4. You should aim for a model where the number of distinct values in a column is small.

5. You should always start with 1-to-many relationships and use the other types only when you fully understand the implications for your model and calculations. If you don't, then be prepared to see things which will be inexplicable (to you).

As for your last question.... Yes, if you have many similar files, you should consolidate them into one entity and load int Power BI. Use Power Query to massage the data into the STAR SCHEMA model.

Best

Darek

[Anonymous]

Hi,

Thank you very much! I will take all this on board and have a look at the links you posted.

Thanks again for your help 🙂