topic Re: Running total between two dates in DAX Commands and Tips

Running total between two dates

BREH95 — Thu, 11 Jul 2024 17:54:00 GMT

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

Re: Running total between two dates

lbendlin — Thu, 11 Jul 2024 23:25:38 GMT

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.

Re: Running total between two dates

BREH95 — Fri, 12 Jul 2024 10:23:31 GMT

Hello lbendlin ,

Thanks for your answer 🙂

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

Re: Running total between two dates

lbendlin — Fri, 12 Jul 2024 11:22:12 GMT

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

Re: Running total between two dates

BREH95 — Mon, 15 Jul 2024 08:55:28 GMT

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

Re: Running total between two dates

Anonymous — Mon, 22 Jul 2024 02:47:35 GMT

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.