Join and model the data with different granularities

Hey kobkabnaja ,

this article explains how to build your semantic model if fact tables have different granularities: https://www.daxpatterns.com/budget/

You have to create at least on dimension table "Calendar", or how however you call it. This table connects to both fact tables. Most likely you need to create a measure that prevents the duplication of the the budget value, e.g. dividing the yearly value by 12). But then you also use the word allocation. As alway it depends, if allocating a yearly budget to monthly values is part of your business process than of course it makes sense, if not then I would not recommend to allocate a yearly value to the month granularity just for the sake of having similar granularity. From my understanding this would create a third fact table, but depending on the granularity of the budget value it is also possible to create a new column in the expenditures table.

Consider to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the pbix file to OneDrive, Google Drive, or Dropbox and share the link.

Regarding the chart, the below chart is leveraging the "" chart type, the line represents the averaged monthly Sales Amount, this is done for demonstration purposes. The average value can represent the yearly budget value (value divided by 12):

Hopefully, this provides some ideas of how to tackle your challenge.

Regards,

Tom