Data Action: Calculate amount based on monthly prices and daily quantities

[ThomasK]

Hi experts!

I´ve data model on daily granularity.

• Quantities are stored on daily level
• Prices are stored on the first of each month
• Goal:
  Calculate Amount (per day) = Quantities (per day) x Price (per corresponding month)

What´s the easiest and most performant way to achieve this?

Here is my (sample) model:

Story with sample data:

As you can see for data on Feb 10th and 23rd the price from Jan instead of Feb is taken

Here is the data action to calculate AMOUNT:

Reason for the wrong calculation is the FIRST function, which returns the first day of the year only. Not aware of a function which returns the first day of the month.
However, AMOUNT should be calculated on daily level as QUANTITY (daily level) x PRICE (monthly level).

If possible I´d like to avoid a FOREACH loop because of data volume and performance. Any ideas to achive this?

Thank you,
Thomas

[ThomasK]

Any ideas to calculate amount based on monthly prices and daily quantities?