cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prateekraina New Contributor
New Contributor

Running Line chart based on Time slicer

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Running Line chart based on Time slicer

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.

 

re1.PNG

 

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.

 

r1.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

2 REPLIES 2
v-ljerr-msft Super Contributor
Super Contributor

Re: Running Line chart based on Time slicer

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.

 

re1.PNG

 

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.

 

r1.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

prateekraina New Contributor
New Contributor

Re: Running Line chart based on Time slicer

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