Create Data Range filters using one date column in PowerBI

[SaiPerumal123]

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

• 

[SaiPerumal123]

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)

[SaiPerumal123]

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]

Hi   @SaiPerumal123 

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

[SaiPerumal123]

Hi Jing,

I tried using the same but i am getting blank records.

I tried using userelationship function, keep fitlers, treatas function, nothing has worked on 

Reason: I dont have active relationship with st_sessions_new and Dimcompare Table.

Below are the dax i tried:

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

=============

Sessions_Denominator =
VAR vStartDate = MIN(DimCompareDate[compareDate])
VAR vEndDate = MAX(DimCompareDate[compareDate])
RETURN
CALCULATE(
DISTINCTCOUNT('st_sessions_new'[session_id]),
FILTER(
ALLSELECTED('st_sessions_new'),
'st_sessions_new'[date] >= vStartDate &&
'st_sessions_new'[date] <= vEndDate
)
)

================

Sessions_Denominator =
VAR vStartDate = MIN(DimCompareDate[compareDate])
VAR vEndDate = MAX(DimCompareDate[compareDate])
RETURN
CALCULATE(
DISTINCTCOUNT('st_sessions_new'[session_id]),
KEEPFILTERS(
'st_sessions_new'[date] >= vStartDate &&
'st_sessions_new'[date] <= vEndDate
)
)

=====

Thanks in advance if you could help me on .

Cheers,

SaiPerumal

[Anonymous]

Hi @SaiPerumal123  Can you please show the current relationships in your model? 

[SaiPerumal123]

Hi Jing,

Here is the DM used.

I tired all feasible ways like userelationship function, keep fitlers, treatas function, nothing has worked on so far.

Example:

AOV4num=

DIVIDE(CALCULATE(

    AVERAGE(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]) &&

        st_sessions_new[totals_total_transactions_revenue] > 0

    )

),1000000)

==========

AOV4den =

VAR vStartDate = MIN(DimCompareDate[Date])

VAR vEndDate = MAX(DimCompareDate[Date])

RETURN

DIVIDE(CALCULATE(

    AVERAGE('st_sessions_new'[totals_total_transactions_revenue]),

   ALLEXCEPT('st_sessions_new', 'st_sessions_new'[channel_grouping]),

    'st_sessions_new'[date] >= vStartDate,

    'st_sessions_new'[date] <= vEndDate

),1000000)

with the above DAX (AOV4den), i tried all possible ways, but i am not getting the correct result due to no direct relationship with "st_sessions_new table" and "DimCompareDate"table.  

Thanks in advance.

cheers,

SaiPerumal. 

[SaiPerumal123]

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]

Hi   @SaiPerumal123  

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]

Hi @SaiPerumal123 

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!