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 t...
  • 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.

     

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.

BREH95's avatar
BREH95
Regular Visitor
2 years ago

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's avatar
    Anonymous
    Not applicable
    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.