Running Line chart based on Time slicer

[prateekraina]

Hello All,

I need to create a line chart for below data with Time on Axis and NoOfPeople on Values:

Camera	NoOfPeople	Time      ID
Cam 1	10	        10:10 AM   1
Cam 1	3	        10:15 AM   2
Cam 1	44	        10:20 AM   3
Cam 1	2	        10:25 AM   4
Cam 1	3	        10:30 AM   5
Cam 1	55	        10:35 AM   6
Cam 1	4	        10:40 AM   7
Cam 1	44	        10:45 AM   8
Cam 1	33	        10:50 AM   9

Problem Statement:
I have a line chart and a slicer in which Time is selected

If i choose 10:25 AM in my Time Slicer i should only see data plotted in Line Chart till 10:25 AM i.e. Last 5 entries are not plotted.
Similarily if choose 10:15 AM in my Time Slicer i should only see data plotted for 1st two entries and so on.

Can anybody suggest any trick for doing this?

Prateek Raina

[v-ljerr-msft · Accepted solution]

Hi @prateekraina,

Based on my test, you should be able to follow steps below to get your expected result. 

1. Add a new table with all possible Time value, and make sure there is no relationship between the two tables.

2. Use the formula below to create a new measure.

Measure =
CALCULATE (
    SUM ( Table1[NoOfPeople] ),
    FILTER ( Table1, Table1[Time] <= MAX ( Table2[Time] ) )
)

3. Then you should be able to show the Time column from your original table as Axis, and the measure as Value on Line Chart to get your expected result.

Here is the sample pbix file for your reference. 

Regards

[prateekraina]

Thanks a lot for this @v-ljerr-msft.