Prorated Monthly Target from Lump Yearly Target

Brotedo — Wed, 07 Sep 2022 13:21:01 GMT

I have a yearly target that I distribute into a monthly cumulative target. I'm using a matrix that resets once a year, and just uses Jan-Dec as columns. Currently looks like this

The code is this:

MonthlySalesTargetEven = (sum(Salesjoin[Forecast2022])/12)* max(MonthIndexOrder[Index])

The Forecast2022 table just assigns lump yearly numbers to associates. This is MonthIndexOrder:

However, this code currently assigns all of the current month target (as of right now, September) as soon as there is data for the month, but obviously the actuals are coming in daily, so there's a huge gap until the end of the month.

How do I get the monthly targets to distribute as above, with the current month's value prorated based on what day of of the month it is?

Re: Prorated Monthly Target from Lump Yearly Target

daXtreme — Wed, 07 Sep 2022 18:49:42 GMT

Create a table with a day-granularity instead of month and distribute the values across days. If you then mark your days with a column that tells you which day is in the past (+present) and which is in the future, you'll be able to slice by this "timeslice" and you'll get what you want for the very latest month.

topic Re: Prorated Monthly Target from Lump Yearly Target in DAX Commands and Tips

Prorated Monthly Target from Lump Yearly Target

Re: Prorated Monthly Target from Lump Yearly Target