Forum Discussion

anandgovindaraj's avatar
anandgovindaraj
Frequent Visitor
7 years ago
Solved

Dynamic date time range for line plot

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!

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    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's avatar
    parry2k
    7 years ago

    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] ) ))

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    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's avatar
      parry2k
      Super User
      7 years ago

      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] ) ))