Unable to calculate variance between ytd and full month last year

User_PKXZP

Variance-

A= sales ytd ( ex: if you are passing current month it would be upto Jun-11-2017.

B=sales for previous year ( this would be sales for jun-2016 entire month, not ytd)

((A-B)/B)*100

Issue is with the calculation of B here. I am able to get the number of days for 'B' here which is the no. of selling/business days from feb-jun 2016 (our fiscal year starts from feb) using

sales <= period number but unable to assign the year here if I use period number. It can be period number of any year. Please see below for the logic i used.

case when    VALUEOF("Current_FP") = '@{P1}' THEN

((FILTER("Customer Transactions Fact"."Sales $" USING

("Invoice Post Fiscal Date"."Invoice Post Fiscal Month" = '@{P1}{Jun-2017}'))/  CAST( VALUEOF("SELLING_DAYS_YTD") AS DOUBLE))-

FILTER("Customer Transactions Fact"."Sales $" USING ("Invoice Post Fiscal Date"."Invoice Post Period Number" <= (@{PNum}{5})))

/

FILTER("Customer Transactions Fact"."Sales $" USING ("Invoice Post Fiscal Date"."Invoice Post Period Number" <= (@{PNum}{5}))))*100

else

((FILTER("Customer Transactions Fact"."Sales $" USING ("Invoice Post Fiscal Date"."Invoice Post Period Number" <= (@{PNum}{5})))-

FILTER("Customer Transactions Fact"."Sales $" USING ("Invoice Post Fiscal Date"."Invoice Post Period Number" <= (@{PNum}{5}))))/

FILTER("Customer Transactions Fact"."Sales $" USING ("Invoice Post Fiscal Date"."Invoice Post Period Number" <= (@{PNum}{5}))))*100

end

Thomas Dodds

1. Properly formed calendar dimension with LY keys on row

2. Alias your fact and join dates on LY keys in Calendar

Now you have canonical time and 2 logical facts to work with Current Year and Last Year ...

Your question is similar to:  Canonical Time set up for a fact. error OBIEE 12c   take a look here and post back with further questions.