Forum Discussion
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...
Hi Anonymous Can you please show the current relationships in your model?
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.