Running total between two dates

[BREH95]

Hi ,

I have the following structure for my dataset : 

1 line per contract  , 

-  contract ID 

- Start date

- End Date

- Monthly amount

I'd like to make a chart where i can see the running total  by month ( calculated between start and end date for each contract ) 

I tried to create a table date  ,I linked this table to start and end date , then i disabled theese links.

I tried to do something  like  

MeasureTest = if ( ('date'),'date'[date]<= Startdate  && ('date'),'date'[date]<= Enddate , Monthly amount , 0 )

 I don't know if I need to generate lines between start date to end date or if i can do it with one formula ... 

Thx for your help

[lbendlin]

Assuming your Monthly Amount is due regardless of when in the month the start and end dates are you can use DATEDIFF. But you need to leave your calendar table disconnected.

[BREH95]

Hello @lbendlin ,

Thanks for your answer 🙂 

Your solution works perfectly if i select 1 contract but  doesn't work if multiple selected .. 

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[BREH95]

Here are my data , 

I need to calculate  the end date  ( I use dateadd , with duration in month ) 

then i'd like to generate this data structure for each contract 

and the resultat should look like   this

[Anonymous · Accepted solution]

Thanks for the reply from@lbendlin , please allow me to provide another insight:

Hi, @BREH95 

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2.Create calculated table references:

time = 
CALENDAR (
    MIN ( 'Table'[START_DATE] ),
    MAX ( 'Table'[START_DATE] ) + MAX ( 'Table'[duration] ) - 1
)

result =
FILTER (
    CROSSJOIN ( 'time', 'Table' ),
    'time'[Date] >= 'Table'[START_DATE]
        && 'time'[Date] <= 'Table'[START_DATE] + 'Table'[duration]
)

3.Here's my final result, which I hope meets your requirements.

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.