Forum Discussion

HQ_Dev_Prod's avatar
HQ_Dev_Prod
Cloud Apps
05-14-2025
Solved

Date range comparative analysis

I'm trying to compare the price between two dates within a date range. 

Here is an example of what I am attempting:

  • My date range is Jan 2024 to June 2024
    • Field 1: KPI, Table Column, Field shows the average price in Jan 2024
    • Field 2: KPI, Table Column, Field shows the average price in June 2024
  • I change the date range to March 2024 to May 2024
    • Field 1: KPI, Table Column, Field shows the average price in March 2024
    • Field 2: KPI, Table Column, Field shows the average price in May 2024

Is there a way I can show this? I've tried a few different data functions within the widget function as well as filtered measure plugin.

One thing I was thinking, is it possible for the date range to auto-populate the filtered measures?

Such as:

  • I duplicate the date field in the Elasticube twice. Once is for Period1Date and the other Period2Date.
  • Period1Date would auto populate to be Jan 2024 and Period2Date would autopopulate to June 2024
  • Then I would use these two date fields in the widget expression
Dashboards & Reporting
Data Models
  • AssafHanina's avatar
    AssafHanina
    05-26-2025

    hey HQ_Dev_Prod ,

    thanks for sharing the full details. 
    From the understating, it's not a comparative dashboard where users can filter 2 custom range of Date filters. instead, users would like to filter on a Period meaning, Single date Filter. 

    Assuming using a Revenue as Metric, Users would like to see the KPI for Revenue for the entire period, and break by the Revenue for multiple Dimensions. 

    Additionally, users would like to be able to Review the Growth from the First Period (i.e. Jan) to the Last Period (i.e Jun) in respective to the Date filter. meaning if the range is different (Jan - March) the Growth calculation is Rev(March) / Rev(Jan) 

    For getting this, it's require to use a filter measure (not the plugin) to Get the Minimum and Maximum of Date according to the date Period. 

    Please find an example on behalf of the Sample Retail Datasource:

    • Open the Sample Retail datasource and add Custom Column: Month_year column to Dimdate table as the following calculation: getyear(date) * 100 + getmonth(date) 

       

    • Import the dash file
    • Review the Dashboard Formula related for Min and Max:
      It's using Top/Bottom 1 to filter the Max(Month_year)

       

    • Try to apply the same on your side 

    Dashboard Output:

    • Red Present Min Period 
    • Green Present Max Period
    • Total Order Revenue relate to the entire period

    Please note that it's a sample use case and may not work across multiple use case

    Best regards

7 Replies

  • DRay's avatar
    DRay
    Admin
    06-03-2025

    Hi HQ_Dev_Prod​,

    I’m following up to see if the solution offered by AssafHanina​ worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.

  • AssafHanina's avatar
    AssafHanina
    Sisense Employee
    05-15-2025

    hey HQ_Dev_Prod ,

    the filter measure plugin allow you the option to slice the same KPI for two different date filters.
    For example:

    1. Field 1: KPI, Table Column, Field shows the average price in Jan 2024 - Filter on Date = 01/2024
    2. Field 2: KPI, Table Column, Field shows the average price in June 2024 - Filter on Date_Compare = 06/2024

    Please find the related post with an example of applying multiple date filters using the filtered measure plugin 

    Best regards

  • DRay's avatar
    DRay
    Admin
    05-19-2025

    Hello HQ_Dev_Prod,

    I’m following up to see if the solution offered by AssafHanina worked for you.

    If so, please click the 'Accept as Solution' button on the appropriate post. That way other users with the same questions can find the answer. If not, please let us know so that we can continue to help.

    Thank you.

  • HQ_Dev_Prod's avatar
    HQ_Dev_Prod
    Cloud Apps
    05-20-2025

    DRay and AssafHanina 

    Still testing it out. I was able to duplicate the date field in the elasticube for comparative analysis and was able to use it as a filter to compare two dates, but I wasn't able to use it in a manner to auto calculate the first period vs last period in a given date range.

    Example:

    - Range = Jan to June

    - I can see average price for this full period

    - But I want to see the price in Jan and then the price in June to compare. 

    • AssafHanina's avatar
      AssafHanina
      Sisense Employee
      05-20-2025

      Hey HQ_Dev_Prod ,

      Based on the Use case described:
      Range = Jan to June: does the range require? 
      Since the Compare is month (Yan) to month (June), the implementation using the filter measure:

      • Add 2 filters: Date and Date_compare (the duplicated column in the EC)
      • Set Date filter = Jan (01/2024 for example)
      • Set Date_Compare filter = June (06/2024 for example)
      • Create a new formula and add the Metric to Compare 
      • Add a measure filter to the metric as the following example: (sum(total amount),@date)
      • add the same metric to the calculation (Assuming the calculation as the following: total amount(Jan) / total amount(June)) and add a measure filter for the metric (sum(total amount),@date_compare)
      • final output should be: (sum(total amount),@date) / (sum(total amount),@date_compare)

      Best regards

       

      • HQ_Dev_Prod's avatar
        HQ_Dev_Prod
        Cloud Apps
        05-21-2025

        AssafHanina thank you for the detailed step by step! 
        I was able to successfully build it this way. However, it requires the user to manually select @Date and select @Date_Compare.

        Would it be possible for the start and end date be set automatically based on the selected date range? 

        Such as:

        • EC data contains date. Duplicate twice, one that is @StartDate and another that is @EndDate.
        • User selects date range to be January to June. (This selection does change dashboard, except for the widgets that contain @StartDate and @EndDate in the function.)
        • @StartDate auto selects to = Jan (Does not change dashboard data. Only when used in a function.)
        • @EndDate auto selects to = Jun (Does not change dashboard data. Only when used in a function.)

        This way, in one dashboard I can see total values during this time period. With a comparative analysis between Start (Jan) and End (Jun).