cancel
Showing results for 
Search instead for 
Did you mean: 

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

ThomasK
Active Participant
0 Kudos
97

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:

ThomasK_0-1738862255217.png

Story with sample data:

ThomasK_1-1738862320708.png

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:

ThomasK_2-1738862445623.png

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

Accepted Solutions (0)

Answers (0)