Forum Discussion

BREH95's avatar
BREH95
Regular Visitor
2 years ago
Solved

Running total between two dates

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
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks for the reply fromlbendlin , 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.

     

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    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's avatar
      BREH95
      Regular Visitor
      2 years ago

      Hello lbendlin ,

       

      Thanks for your answer 🙂 

       

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

      • lbendlin's avatar
        lbendlin
        Super User
        2 years ago

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