Forum Discussion
Date Filtering not working in Measure
There's a lot going here, and the images are not readable. Most important thing here is that the way you are using variables here will not work. The calculation is done when the variables are defined, the CALCULATE statements will not alter the value. Try something like below.
Rx Granted = IF(SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last 7 Days", CALCULATE(DIVIDE(SUM(FCT_Operational_KPIs[VALUE]),SUM(FCT_Operational_KPIs[VALUE]), 0) ,FCT_Operational_KPIs[KPI] = "RX Granted %" ,KEEPFILTERS('DIM Date'[This Week] = "This Week")) -- Ensures filter propagates + 0) ,IF(SELECTEDVALUE('Time_Intelligence'[Time Calculation]) = "Last week", CALCULATE(DIVIDE(SUM(FCT_Operational_KPIs[VALUE]),SUM(FCT_Operational_KPIs[VALUE]), 0) ,FCT_Operational_KPIs[KPI] = "RX Granted %" ,KEEPFILTERS('DIM Date'[Last Week] = "Last Week")) -- Ensures filter propagates + 0) )
SUMESHKUMAR22 , Try using
dax
Rx Granted =
VAR Denominator =
CALCULATE(
SUM(FCT_Operational_KPIs[VALUE1]),
FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %")
)
VAR Numerator =
CALCULATE(
SUM(FCT_Operational_KPIs[VALUE]),
FILTER(FCT_Operational_KPIs, FCT_Operational_KPIs[KPI] = "RX Granted %")
)
VAR div_ =
IF(
ISBLANK(Denominator) || Denominator = 0,
0,
DIVIDE(Numerator, Denominator, 0)
)
RETURN
SWITCH(
SELECTEDVALUE('Time_Intelligence'[Time Calculation]),
"Last 7 Days",
CALCULATE(
div_,
FILTER('DIM Date', 'DIM Date'[This Week] = "This Week")
),
"Last week",
CALCULATE(
div_,
FILTER('DIM Date', 'DIM Date'[Last Week] = "Last Week")
),
div_ -- Default case if no match
)
HI bhanu_gautam , I tried this approach earlier but Its the same no change in the filtering showing all dates.
