Iif statement on comparing dates

Question

I have a lot of KPIs where I have to measure the amount of hours between two dates, for example how long it took between the order date and the fulfillment date. Until now, I have added calculated columns in my fact table where I put a 1 when it's within the limits of the KPI and 0 when it's longer. The problem is, the amount of hours/days change often so this doesn't make much sense when at first the KPI is set to 2 hours and then it's decided the order need to be fulfilled within 3 hours. 

So next I thought I'd make calculations in my cube and there's where I got stuck. For example, I want to make a calculation that counts the number of orders that have been fulfilled within 2 hours. I have a field that contains the hours that it took to fulfill, so that's the only field where I'd have to look. In my calculation, I tried something like this:

Iif([Measures].[Fulfillment hours] < 2, 1, 0)

This doesn't give me any values though... 

Can anyone point me in the right direction of how I could make a calculation for this purpose?

Thanks!

---

-

Answer 1

How are you browsing this KPI? What are your dimensions? I think, you are looking at this calculation at a consolidated level across dimensions and hence the Fullfillment hours always goes beyond 2. You should scope this calculation on the Order dimension (excluding the All member).

---

amit kar