Set Analysis Help

[#1]

=Sum({$<Quarter={'Q4'},{Book1})> - sum({$<Quarter={'Q3'},{Book1} >} grossamount * 4)

Hi All,

Can someone please guide me towards the correct way of doing this SET expression. I have created a Bookmark called Book1 for the accounts needed for revenue calculation. I want to find the difference of income between the two quarters and times it by 4.

Requirements: Difference of income (book1 represents income accounts) between Quarter 4 and Quarter 3 times it by four.

Please shade some light.

Thanks in advance,

[sunny_talwar]

To make the dollar sign work in your previous expression, you would do this:

=Money(Sum(({[Book1]<QuarterNum={"$(=Max({$}QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount) - Sum({[Book1]<QuarterNum={"$(=Max({$}QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} grossamount)) * 4)

Basically wrapping Money() function around the whole thing (including the 4)

and for your second request, may be as jagan‌ prescribed. Let us know if that doesn't work.

[#3]

Hi Guys,

thanks for your help. I just need the last piece of the pie from you. Lets say I want to compare 2016 Q1 to 2015 Q4. Will it be possible with the above Jagan formula? Currently my formula doesn't include this part. It only provides me with the difference of quarters if it's the same year. What I'm humbly requesting is a rolling quarter change where if I click on Q1 2016 it will provide me with the difference of Q1 2016 - Q4 2015.

Thanks in advance!!!!

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum)-1)"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.expenses))

[sunny_talwar]

Try this:

=Money((Sum({[Book1]<MC.QuarterNum={"$(=Max({$}MC.QuarterNum))"}, [MC.DimYear] ={"$(=Max({$}[MC.DimYear]))"}>} MC.grossamount) -

Sum({[Book1]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.grossamount)) * 4 /

Sum({[Book2]<MC.QuarterNum={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, 4, Max({$}MC.QuarterNum)-1))"}, [MC.DimYear] ={"$(=If(Max({$}MC.QuarterNum) - 1 = 0, Max({$}[MC.DimYear]) - 1, Max({$}[MC.DimYear])))"}>} MC.expenses))

[#5]

Hi Sunny,

Can you briefly explain to me the formula itself and the reasons for - 1 = 0, 4 and -1 = 0? Does it tell the formula to look for the numbers for the previous quarter even if the quarter might be from another year(e.g. Q4 of 2015)?

Thanks for ALL your help Sunny!!!

[sunny_talwar · Accepted solution]

This is saying if QuarterNum selected is 1 then instead of showing 0, show 4 for the QuarterNum and same logic for the year. If the QuarterNum is 1, we need to go back one year and use the 4th quarter.

[#7]

Thanks Jagan and thanks a bunch Sunny. Without your help I would have not been able to tackle this formula.

Thanks again!!!!!!!!