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 D...
Need Help
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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

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