Line Chart x axis every day

[TOGA]

Hi,

I'd like to create a line chart with every day in 2022 being on the x axis. 

I am in sales and we have various service contracts that incept during this year. Every contract has it's own amount and I would like to have a cumulative line chart that shows the cumulative value of service contracts agreed this year. The y axis should be the cumulative sales amount, the x axis should be 2022, but with every day basically as a data point (and not months or quarter) to see how the sales volume is increasing over the course over the year. 

So in below table for example, for five days Jan 1 to Jan 5 the value would be 0. On Jan 6 the value would jump to $45,000 and would remain at $45,000 until Jan 15. On Jan 16 we signed another contract worth $50,000 so the cumulative line would jump to $95,000. And so on. 

Account	Inception Date	Value New Service Contract	Expiry Date of old contract	Expiring Service Contract Value
A	1/6/2022	45000	1/5/2022	40000
B			1/10/2022	50000
C	1/16/2022	50000	1/15/2022	55000
D	1/25/2022	60000
E			2/1/2022	60000
F	2/3/2022	35000	2/10/2022	30000
G			2/10/2022	35000
H	2/20/2022	40000
I	2/27/2022	45000	2/26/2022	20000
J	3/6/2022	60000	3/5/2022	35000
K	3/20/2022	20000

In the same line chart I would like to have another line showing the cumulative value of the expiring service contracts. So similar, to have the line of the sum of all the expiring values over 2022, with every day as one data point. 

I am really struggling to make this work - can someone help me please?
Thank you!

[v-zhangti · Accepted solution]

Hi, @TOGA 

To calculate the cumulative value, try the following method.

Column:

Cumulative value new =
CALCULATE (
    SUM ( 'Table'[Value New Service Contract] ),
    FILTER ( 'Table', [Inception Date] <= EARLIER ( 'Table'[Inception Date] ) )
)

Cumulative value due =
CALCULATE (
    SUM ( 'Table'[Expiring Service Contract Value] ),
    FILTER (
        'Table',
        [Expiry Date of old contract]
            <= EARLIER ( 'Table'[Expiry Date of old contract] )
    )
)

However, it is not possible to make the date table active with two date columns at the same time. Therefore, it is not possible to display the complete line chart in the same chart. In this case, the line graph can only be viewed separately.

Best Regards,

Community Support Team _Charlotte

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

[JirkaZ]

Shouldn't a measure like this do the trick? 

CALCULATE(SUM(Amount), [Inception Date] <= MAX([Date]))