Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Create Data Range filters using one date column in PowerBI

Hi,

I have Table A with Date and Session columns. Using Date column i have to create 4 different Date range filters like Start Date, End Date, Compare Start Date, Compare End Date.

  • Compare Start Date, Compare End Date --> should shows previous months selections 
  • I have created seperate Date columns and added as slicers , but when i select , i can see only 1 date but not all the dates for all 4 slicers.
  • Based on the selection of dates, there is table matrix which will reflect the changes in values.
  • Kindly help me to create the similar slicer in powerbi.

Thanks in Advance

  •  

 

Need Help

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    If you want to filter a date range with only one date column, it's recommended to modify the date slicer to date range mode (Between style). Create a numeric or date range slicer in Power BI - Power BI | Microsoft Learn

     

    Also, there are some custom visuals that could be an alternative. 

    Date Picker

    Timeline Slicer

    Your Timeline Slicer

     

    If I understand it correctly, you want to compare data in two different periods. In this case, you need to have two date tables for two date range slicers. Then use measures to calculate the results for comparing. Here is a similar blog for your reference: How to compare data in different date ranges - Microsoft Fabric Community

     

    If you hope to use four slicers and their picked date should be independent, you need to have four date tables which are disconnected with each other. Once any date table is connected with another one (or two slicers share the same date column), they will be affected by the filtering interaction behavior between the slicers. 

     

    Best Regards,
    Jing
    If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks Jing.

    I have sorted out Range filter now. I have an other query 

     

     

    Need to calculate Revenue based on the dates (Screenshot for example)

    Start and End Date range

    Compare Start and end date Range

    Session % = Dates bw start and end of revenue calculation/ dates bw compare start and end Date Range of revenue calculation

    DAx Used :

    Revenue4num =
    divide(CALCULATE(
        SUM(st_sessions_new[totals_total_transactions_revenue]),
        FILTER(
            st_sessions_new,
           st_sessions_new[date] >= SELECTEDVALUE(DimDate[StartDate]) &&
            st_sessions_new[date] <= MAX(DimDate[StartDate])
        )
    ),1000000)
    Revenue4den =
    divide(CALCULATE(
        SUM(st_sessions_new[totals_total_transactions_revenue]),
        FILTER(
            st_sessions_new,
           st_sessions_new[date] >= SELECTEDVALUE(DimDate[CompareStartDate]) &&
            st_sessions_new[date] <= MAX(DimDate[CompareStartDate])
        )
    ),1000000)

    REvenud_diff1%=
     DIVIDE(Revenue4num, Revenue4den, 0) - 1
    Thanks in advance.
    Cheers,
    SaiPerumal
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi   Anonymous  

      You need two date tables, one for the first date slicer and the other for the comparison range slicer. 

      Relationships:

      As the relationship exists between DimDate table and the fact table, the revenue measure could be:

      Revenue4num = SUM('Table'[Revenue]) 
       
      For the other measure, it uses the CompareDate table's date column. The CompareDate table should be disconnected with other tables. So the measure could be:
      Revenue4den =
      VAR vStartDate = MIN(CompareDate[Date])
      VAR vEndDate = MAX(CompareDate[Date])
      RETURN
      CALCULATE(SUM('Table'[Revenue]),ALLEXCEPT('Table','Table'[Country]),'Table'[Date]>=vStartDate, 'Table'[Date]<=vEndDate) 
       
      Result:

       

      I have attached the demo pbix at bottom, hope it would be helpful.

       

      Best Regards,
      Jing
      If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks Jing.
    One question is that formula is restricted to Channel_grouping only.
    But I have additional filters added like Source, medium,campagin.When i select source as filter, session% values are not showing the correct results.(Attached screenshot for reference)

    1. When i select StartDate filter range, Sessions calculations is working fine.
    2. When i select CompareStartDate filter range, Sessions% calculations is not working as expected.
    So, i removed the exception from the below dax which is not correct result.
    Dax===

    Sessions4num =
    CALCULATE(
    DISTINCTCOUNT('st_sessions_new'[session_id]),
    'st_sessions_new'[date] >= SELECTEDVALUE('DimDate'[StartDate])
    )

    Sessions4den =
    VAR vStartDate = MIN(DimCompareDate[Date])
    VAR vEndDate = MAX(DimCompareDate[Date])
    RETURN
    CALCULATE(DISTINCTCOUNT('st_sessions_new'[session_id]),
    ALLEXCEPT('st_sessions_new','st_sessions_new'[channel_grouping]),
    'st_sessions_new'[date]>=vStartDate, 'st_sessions_new'[date]<=vEndDate)

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Dax used are:

      Sessions% = DIVIDE([Sessionsnum],[Sessionsden],0) - 1
      Session_numerator=
      CALCULATE(
      DISTINCTCOUNT('st_sessions_new'[session_id]),
      'st_sessions_new'[date] >= SELECTEDVALUE('DimDate'[StartDate])
      )

      Sessions_Denominator =
      VAR vStartDate = MIN(DimCompareDate[Date])
      VAR vEndDate = MAX(DimCompareDate[Date])
      RETURN
      CALCULATE(DISTINCTCOUNT('st_sessions_new'[session_id]),
      ALLEXCEPT('st_sessions_new','st_sessions_new'[channel_grouping]),'st_sessions_new'[date]>=vStartDate, 'st_sessions_new'[date]<=vEndDate)

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi   Anonymous 

         

        Please try this. I modified it to use REMOVEFILTERS function instead. This function will remove the filter from the DimDate table while respecting all other filters in the current context.

         

        Sessions_Denominator =
        VAR vStartDate = MIN(DimCompareDate[Date])
        VAR vEndDate = MAX(DimCompareDate[Date])
        RETURN CALCULATE(DISTINCTCOUNT('st_sessions_new'[session_id]),REMOVEFILTERS(DimDate),'st_sessions_new'[date]>=vStartDate, 'st_sessions_new'[date]<=vEndDate) 

         

        Best Regards,
        Jing