Acummulate values until a date of specific lines

[MQS_Sistemas]

I will try to explain the problem that I'm having to make a measure.

This is the data I have in my model:

Some Contracts, the monthly payment Value, the Due Date of this monthly payment and the Contract Expiration Date.

CONTRACT	VALUE	DUE DATE	CONTRACT EXPIRATION DATE
A	50	01/01/2021	10/02/2021
A	50	01/02/2021	10/02/2021
A	50	01/03/2021	10/02/2021
A	50	01/04/2021	10/02/2021
B	80	01/01/2021	31/12/2021
B	80	01/02/2021	31/12/2021
B	80	01/03/2021	31/12/2021
B	80	01/04/2021	31/12/2021
B	80	01/05/2021	31/12/2021

I want to create a line chart that contains in the X axis the due date and in the Y axis the value acummulated. The problem is that this value has to "disappear" after the expiration date of the contract, and not accumulate anymore. So, if we take the Contract A, for example:

It has to acummulate until 10/02/2021, so, after this date (01/03/2021), the value for Contract A will be 0, and it will not show a value even for the next months.

So, the result that I want for the line chart would be like this:

MONTH	VALUE	**Contains
jan/21	130	CONTRACT A + B
feb/21	260	CONTRACT A + B (JAN + FEB)
mar/21	240	CONTRACT B (JAN + FEB + MAR) - Contract A has  expired
apr/21	320	CONTRACT B (JAN + FEB + MAR + APR)
may/21	400	CONTRACT B (JAN + FEB + MAR + APR + MAY)

If there are some doubts about the problem, please tell me so I can help to improve the details.

I appreciate if someone helps me, cause I've been dedicated to this problem since 2/3 weeks ago.

Kind Regards,

Renan

[v-janeyg-msft]

Hi, @MQS_Sistemas 

I think you can create a date table additionally, and don't need to build relationships, then format the date and sort by column(The date order of the text type will change). Then create a measure to show results.

Like this:

Table 2 = CALENDAR(MIN('Table'[DUE DATE]),MAX('Table'[DUE DATE]))
monthyear = FORMAT('Table 2'[Date],"mmm yy")
monthday = DATE(YEAR('Table 2'[Date]),MONTH('Table 2'[Date]),1)

total =
VAR a =
    DATE ( YEAR ( MAX ( 'Table 2'[Date] ) ), MONTH ( MAX ( 'Table 2'[Date] ) ), 1 )
RETURN
    SUMX (
        FILTER ( ALL ( 'Table' ), [DUE DATE] <= a && [CONTRACT EXPIRATION DATE] >= a ),
        [VALUE]
    )

If you still have problems, please feel free to ask me.

Best Regards

Janey Guo

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

[MQS_Sistemas]

Thank you for your answer! It also worked well, but i've had already tried with the previous answer and it worked! But I really appreciate your help Janey!

[Jihwan_Kim · Accepted solution]

Hi, @MQS_Sistemas 

Please check the below picture and the sample pbix file's link down below.

Values Total  Cumulate=

VAR contract =

CALCULATETABLE (

VALUES ( Data[CONTRACT] ),

FILTER (

Data,

Data[DUE DATE] = MIN ( Dates[Date] )

&& Data[CONTRACT EXPIRATION DATE] >= MIN ( Dates[Date] )

)

)

RETURN

IF (

ISFILTERED ( Dates ),

CALCULATE (

SUM ( Data[VALUE] ),

FILTER ( Data, Data[DUE DATE] <= MIN ( Dates[Date] ) ),

contract

)

)

https://www.dropbox.com/s/fjk095xhgti4d4g/mqs.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[MQS_Sistemas]

Thank you so much! It worked well!

[amitchandak]

@MQS_Sistemas , refer to this blog or the attached file can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[MQS_Sistemas]

Thank you so much for the answer!