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

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? 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.