5 Replies Latest reply on Jul 31, 2018 12:32 PM by Mahfooj Khan

# Calculate difference between measures for different periods (month, quarter etc)

Hi All,

I got puzzled with the following:

Have a data like:

 ID Frequency Date Value Dummy1 Monthly 2018-03-01 10 Dummy1 Monthly 2018-02-01 20 Dummy1 Monthly 2018-01-01 30 Dummy2 Quarterly 2018-06-01 100 Dummy2 Quarterly 2018-03-01 200 Dummy2 Quarterly 2018-09-01 300 Dummy3 Annual 2018-01-01 1000 Dummy3 Annual 2020-01-01 1100 Dummy3 Annual 2019-01-01 1200

The view i want to achieve:

 Name 2nd to last value last value current value Diff (current - last) Dummy1 30 20 10 -10 Dummy2 100 200 300 100 Dummy3 1000 1100 1200 100

Any idea how can I achieve this?Many thanks for your help in advance

• ###### 1. Re: Calculate difference between measures for different periods (month, quarter etc)

Hi Kamil,

How are you Getting Last as you are showing?

and What is your Date format...Is This entire data for January Month?

Thanks

Deepak

• ###### 2. Re: Calculate difference between measures for different periods (month, quarter etc)

Hi Kamil,

Is that what you wanted?

Note: Your output for Dummy 3 is not correct as per logic. Check again

Find my approach below,

Current Value:

IIF(DATETRUNC('month',[Date])={FIXED [ID]:MAX(DATETRUNC('month',[Date]))},[Value],NULL)

Last Value:

CASE [Frequency]

END

//2nd to last value calculation is based on the last value logic, only difference the interval which is -2

2nd to Last Value:

CASE [Frequency]

END

Finally

Diff (Current - Last):

SUM([Current Value])-SUM([Last Value])

Let us know if this help. Workbook attached for your reference.

Mahfooj

1 of 1 people found this helpful
• ###### 3. Re: Calculate difference between measures for different periods (month, quarter etc)

Or maybe this one?

 ID Frequency Date Value Dummy1 Monthly 2018-01-01 30 Last Dummy1 Monthly 2018-02-01 20 2nd Dummy1 Monthly 2018-03-01 10 Current Dummy2 Quarterly 2018-03-01 200 Last Dummy2 Quarterly 2018-06-01 100 2nd Dummy2 Quarterly 2018-09-01 300 Current Dummy3 Annual 2018-01-01 1000 Last Dummy3 Annual 2019-01-01 1200 2nd Dummy3 Annual 2020-01-01 1100 Current
1 of 1 people found this helpful
• ###### 4. Re: Calculate difference between measures for different periods (month, quarter etc)

This is exactly want I am after, many thanks !

• ###### 5. Re: Calculate difference between measures for different periods (month, quarter etc)

You're welcome