Forum Discussion
Budget Analysis vs Acual Sales
What level of granularity do each of your facts tables have? If they are different (for example, sales data is on a daily basis but budgets are on a monthly basis) you need to adjust the model either using DAX or by creating a period table (two techniques I'm aware of). The first technique is very well explained in the following video by Sam Mckay at Enterprise DNA:
The other method is to create a period table, which is then used to filter the fact tables and the calendar table using a period id such as YearMonth, which I picked up from the very good book by Rob Collie & Avichal Singh "Power Pivot and Power BI: The Excel User's guide to the data revolution" - there is a whole chapter which explains how to work with differing granularities.
What level of granularity do each of your facts tables have? If they are different (for example, sales data is on a daily basis but budgets are on a monthly basis) you need to adjust the model either using DAX or by creating a period table (two techniques I'm aware of). The first technique is very well explained in the following video by Sam Mckay at Enterprise DNA:
The other method is to create a period table, which is then used to filter the fact tables and the calendar table using a period id such as YearMonth, which I picked up from the very good book by Rob Collie & Avichal Singh "Power Pivot and Power BI: The Excel User's guide to the data revolution" - there is a whole chapter which explains how to work with differing granularities.