Hi,

I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/ with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.

I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:

The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). In my mind I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.

Do you have a solution for how I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.

Best regards

Morten

With "budget" table:

and "dates" table:

In "dates" table, two calculated columns: