3 Replies Latest reply on Apr 13, 2017 6:09 AM by Norbert Maijoor

Applying a calculated field to all measure values.

Hi all,

I have been asked to provide some year on year calculations which can be filtered by month. However when looking at the current month i would like to be able to see a comparable range for the previous year.

I.e

If only 14 days have passed in the current month at the moment i see

2017                        2016

Metric       14 days of data         30 days of data

I've created a calculation which calculates the number which the 2016 figure needs to be multiplied by in order to get a comparable figure - Current Month Multiplier =  (Days passed in current month/Total days in current month)

My questions is how can i then apply this to all of the data in this workbook, ideally i'd like to right an 'if' statement such as;

If Year([Date]) = 2016

Then

[Measure Values]*[Current Month Multiplier]

Else

[Measure Values]

End

Any solutions would be great.

Billy

• 1. Re: Applying a calculated field to all measure values.

Hi Billy,

Find my approach as reference below and stored in attached workbook version 9.3 located in the original thread

1. Current Month: if datediff('month',[date],today())=0 then [sales] END

2. Current Month Last Year: if datediff('month',[date],today())=12 and datediff('day',[date],today())>365 then [sales] END

Proof of the pudding;)

Regards,

Norbert

• 2. Re: Applying a calculated field to all measure values.

Hi Norbert, from you example it looks like you are doing this with a single measure (sales) my question is instead of creating a calculated field for each measure, is there a way to create one which will work across all measure.

Hope this makes sense

• 3. Re: Applying a calculated field to all measure values.

Hi Billy,

For a multiple measures you could say

Current month show??: datediff('month',[date],today())=0 drag it to filter and set to "True"

but you will need a seperate sheat for

Current Month Last Year show??: datediff('month',[date],today())=12 and datediff('day',[date],today())>365

You will need to bring both sheets in one dashboard to visualize everything at once.

Hope it is clear.

Regards,

Norbert