Forum Discussion
Distributing multiple values over different time periods
Hello Cmcmahan,
Thank you for your answer.
If I got it right, the measure you provided calculates the expected daily revenue by dividing the total amount by working days.
Maybe I am missing something in your post, but how can I distribute the expected daily revenue among the future working days? At the end I would like to see, that we can expect amount X during (rest of) August, amount Y during September, etc.
Therefore I think it is necessary to distribute the expected daily revenue throughout the future days until the end date is reached. e.g. to calculate the amount for 22.08.2019 Power BI should add up all expected daily revenues with end date >= 22.08.2019.
This is your measure with my data:
Daily Amt =
SUMX (
SUMMARIZE (
'offene Kontrakte_BI';
'offene Kontrakte_BI'[Verkaufsb.];
"Daily Total"; DIVIDE (
SUM ( 'offene Kontrakte_BI'[Contracts EUR]);
CALCULATE (
COUNTROWS ( 'Calendar_working days_BI' );
DATESBETWEEN ( 'Calendar_working days_BI'[Date]; TODAY (); LASTDATE ( 'offene Kontrakte_BI'[Gültig bis] ) );
'Calendar_working days_BI'[WD] = 1
)
)
);
[Daily Total]
)
Would each day not have the same amount? I think I'm missing something in what you're asking.
From what I understand, you have $X as the contract amount. I calculate Y working days between now and the contract end date. I divide $X by Y days to find the amount per day. If you want to figure out the total revenue for a month (let's say September), just multiply by Z working days in September. So $X/Y days * Z days would get you your total expected dollar amount for September.
Otherwise, I may need you to explain the whole distribution of funds thing, since it seems like a very abstract concept right now.
Maybe I am thinking too complicated - I was planning to realise the distribution in a calculated column.
Your suggestion seems to be a measure. I agree that $X/Y days * Z days gives me the right amount, if X is valid for all days of Z.
At this point I am struggling. How to tell Power BI to take only those daily values and multiply them by future working days, where the contract is still valid?
It seems super strange to me to set this up as a calculated column. Would you have the same contract number in multiple rows, along with every working date between start and end of contract, and the expected amount? That seems like a stupidly large amount of data replication.
When you say "if X is valid for all days of Z", how can you tell whether this is true for a given day and contract?
Would it make more sense with your data to take the contract start date instead of TODAY() as the first date, and then do the same math with that as a starting point?
If the solution is a measure or a calculated column does not matter to me.
Does it help to take the contract start date? At the end I will filter on future days anyway.
Do you see the point where I am struggling? I need to make sure that the daily values are considered only for those days, where the date of the day is before (or on) the contract expiry date.
e.g. having a contract with total value 12k and expiry date 13.09.2019 -> daily value (future working days only) = 1k and now I want Power BI to consider this 1k for every day until expiry date; at the end I want to see, that the distributed contract value for August is 2k and for September it is 10k