Comparing running totals of different periods in the same line chart

[monge]

I'm trying to compare running totals of different periods that are user defined through date slicers. The fact table is rather simple, a date column with the selling date and another column with the selling value. Something like this:

There are other tables in the model and all dates are linked to a calendar table. What I'm trying to do is to create two running totals of the "Value" column where the time periods for it are user defined through date slicers, and then use these two measures in the same line chart, even though the time periods might be different. I know I can create the running total using a measure like this

CUMSUM = CALCULATE(
               SUM('FACT TABLE'[Value]),
               FILTER(ALLSELECTED(Calendar), Calendar[Date] <= MAX(Calendar[Date])
              )

 But I'm not sure how to merge together two measures in the same line chart due to the different chosen periods. So, what I want is:

• Create the running total CUMSUM1 of the "Value" column, where the time period for the calculation is defined by date slicer 1;
• Create the running total CUMSUM2 of the "Value" column, where the time period for the calculation is defined by date slicer 2;
• Merge then together in the same line chart even though the time periods are different.

I appreciate any help with this!
Thank you!

[Ashish_Mathur]

Hi,

I cannot visualise that line chart.  If 2 non overlapping line time periods are selected, then what should appear on the X-axis?

[Anonymous · Accepted solution]

Hi, @monge 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measure:

SumValue =
CALCULATE (
MAX ( 'Fact Table'[value] ),
FILTER (
'Fact Table',
'Fact Table'[date]
IN ALLSELECTED ( 'Date 1'[date 1] )
|| SELECTEDVALUE ( 'Fact Table'[date] ) IN ALLSELECTED ( 'Date 2'[date 2] )
)
)

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum