Forum Discussion

TOGA's avatar
TOGA
Regular Visitor
4 years ago
Solved

Line Chart x axis every day

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. 

 

AccountInception DateValue New Service ContractExpiry Date of old contractExpiring Service Contract Value
A 1/6/2022450001/5/202240000
B  1/10/202250000
C1/16/2022500001/15/202255000
D1/25/202260000  
E  2/1/202260000
F2/3/2022350002/10/202230000
G  2/10/202235000
H2/20/202240000  
I2/27/2022450002/26/202220000
J3/6/2022600003/5/202235000
K3/20/202220000  

 

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!

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

     

2 Replies

  • JirkaZ's avatar
    JirkaZ
    Solution Specialist
    4 years ago

    Shouldn't a measure like this do the trick? 

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

  • v-zhangti's avatar
    v-zhangti
    Community Support
    4 years ago

    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.