Forum Discussion
Distributing multiple values over different time periods
How is the first date determined? Is it just TODAY()? It should be easier to get your result by just counting the working days between now and the end date and using that as a divisor.
Assuming you put this measure in a table split up by Contract No for context, something like this should work for both individual rows and the total. My apologies for not using the table/column names you provided, but my German is very poor.
Daily Amt =
SUMX (
SUMMARIZE (
Table1,
Table1[Contract No],
"Daily Total", DIVIDE (
SUM ( [Total Value] ),
CALCULATE (
COUNTROWS ( 'Calendar' ),
DATESBETWEEN ( 'Calendar'[Date], TODAY (), LASTDATE ( Table1[End Date] ) ),
'Calendar'[IsWorkingDay] = 1
)
)
),
[Daily Total]
)
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]
)