SAC Model calculations - YoY (function) and MoM/WoW/DoD

[hadar_k]

How can I create a model calculation for YOY, MoM,WoW,DoD .

my model has sales [measure] per day [Date dimension].

I saw the YOY built in function but thought maybe someone used that Iterate function to build the same for the rest of the granularities.

MoM = (Current Month - Prev. month ) / Prev. month

same for week and day.

[N1kh1l · Accepted solution]

hadarkuint

Except YoY all others have to coded explicitly in the model as below

PQ:

LOOKUP([Measure] ,[d/Date]= Previous("Quarter", 1))

PM:

LOOKUP([Measure] ,[d/Date]= Previous("Month", 1))

PW: This one may require you to have a week level hierarchy or may not be supported

LOOKUP([Measure] ,[d/Date]= Previous("Week", 1))

PD

LOOKUP([Measure] ,[d/Date]= Previous("Day", 1))

Finally

QoQ =( [Measure]-[PQ])/[PQ]

You can repeat for others. This assumes you have date dimension in axis and will calculate QoQ based on the date members in axis. Else you can go for Current period calculations like

CQ

LOOKUP([Measure] ,[d/Date]= Current("Quarter"))

LOOKUP([Measure] ,[d/Date]= Current("Month"))

and so on

QoQ= [CQ]-[PQ]/[PQ]

Br.

Nikhil

[hadar_k]

Hi Nikhil Anand,

Amazing! thanks for the quick response.

everything worked, and planning to implement as a default calculation for each new model going forward.

[N1kh1l]

hadarkuint

Great. Could you close the thread by accepting the helpful answer.

Br.

Nikhil