Forum Discussion
Helper IIIBest Solution for Month to Month Analysis without Causing Duplicate Data Errors.
Hi Lenihan,
What I was suggesting is that with the calculated table you can get all the unique CI Identifier across all your tables.
Then from tables you would create a relationship to the Calculated table.
What that then means is you could then use the Calcualted Table as a filter. And it will filter all the other tables?
Here is a blog post explaining how to create the Calculated Table - Connect any number of tables together via a common column
Super UserHi Lenihan,
Another suggestion that I would make is to create a Date table. By having a Date table it makes it very easy to use the built in time-ingelligence features in Power BI (SAMEPERIODLASTYEAR, PARALLELPERIOD, etc) to compare Month to Month or Cumulative Rolling periods also.
If you need to understand what a Date table does you can read this blog post: Do You Need a Date Dimension?
Next if you now want to go ahead and create a date table, here is my blog post explaining how to easily do this: Power BI – How to Easily Create Dynamic Date Table/Dimension with Fiscal Attributes using Power Query
Then what I would in the Query Editor is because you know when your data is coming in, I would create another column in which you can use the Month and Year to create a Date column. So that the column would look like 01/01/2017 which is effectively made up of "01/" & [Month] & "/"& [Year]
Once the above is done in your Query Editor you can then load the data and create the relationship from your table to your date table.