Dynamic date time range for line plot

[anandgovindaraj]

I have a hourly price data, below is a sample:

Eventdatetime   Price

7/1/2019 0:00    22
7/1/2019 1:00    24
7/1/2019 2:00    24
7/1/2019 3:00    23
7/1/2019 4:00    23
7/1/2019 5:00    25
7/1/2019 6:00    28

I created a simple line plot of price time series and I have a slicer  (between dates) to control the number of days to be  viewed. 

Is it possible to plot, two days ( 48 hours) at a time ? Yes, I can use the slicer for this but the goal is to allow the user to select a day ( using a slicer with list of all dates)  and the plot should show hourly prices for selected day and the next day. 

Thanks!

[MFelix · Accepted solution]

Hi @anandgovindaraj ,

You need to do the following steps:

• Create a calendar table to use as filter (this table must not be related with the dataset)
• Add the following measure:

Price over 2 days = 
VAR SelectedDate =
    MAX ( DimDate[Date] )
VAR Number_of_days = 1 //Used to create number of days to show in chart if want to show 3 days number should be 2
RETURN   
CALCULATE (
        SUM ( EventDate[Price] );
        FILTER (
            ALL ( EventDate[Eventdatetime].[Date] );
            EventDate[Eventdatetime].[Date] >= SelectedDate
                && EventDate[Eventdatetime].[Date] <= SelectedDate + Number_of_days
        )
    )

• Create a slicer with the calendar table
• And a chart with the EventDatetime and the previous measure.

Final result below:

See attach PBIX file.

Regards,

MFelix

[anandgovindaraj]

Thanks!

[MFelix · Accepted solution]

Hi @anandgovindaraj ,

You need to do the following steps:

• Create a calendar table to use as filter (this table must not be related with the dataset)
• Add the following measure:

Price over 2 days = 
VAR SelectedDate =
    MAX ( DimDate[Date] )
VAR Number_of_days = 1 //Used to create number of days to show in chart if want to show 3 days number should be 2
RETURN   
CALCULATE (
        SUM ( EventDate[Price] );
        FILTER (
            ALL ( EventDate[Eventdatetime].[Date] );
            EventDate[Eventdatetime].[Date] >= SelectedDate
                && EventDate[Eventdatetime].[Date] <= SelectedDate + Number_of_days
        )
    )

• Create a slicer with the calendar table
• And a chart with the EventDatetime and the previous measure.

Final result below:

See attach PBIX file.

Regards,

MFelix

[parry2k · Accepted solution]

@anandgovindaraj solution attached, just add a simple measure as below, and create a date table for slicer.

Total Price = 
CALCULATE( SUM( Table1[ Price] ),  
DATESBETWEEN( Table1[Eventdatetime ].[Date], 
MIN( 'Calendar'[Date] ), 
NEXTDAY( 'Calendar'[Date] ) ))