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) )
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)
)HI This solution works as expected but whe I put +0 then it will not respect the TIme calcualtion filter & instead show all data & as I remove the + 0 then it works fine but is there a way we can put 0 for mar 31, Apr01, Apr2 in the below figure.
Data for these 3 dates looks like below as you could see no channel so probably need to figure out a way to handle blank category as it shouldnt be shown on dashboard.
Thanks!
