3 Replies Latest reply on May 17, 2017 9:14 AM by Norbert Maijoor

# When quarter closed show quarter / else show month

Hi All

My data source contains the whole year 2016.

Now there will be added every ended month of 2017. So far until April 2017.

To only show months which can be compared with 2017 I created a filter:

IF MONTH([Date]) <= MONTH(DATEADD('month',-1,TODAY())) THEN 'until current month' ELSE 'show all' END

The costs in 2016 should now be compared with the costs in 2017.

Therefore I made two calculated fields:

[Costs 2016]

IF YEAR([Date]) = 2016 THEN [Costs] ELSE 0 END

[Costs 2017]

IF YEAR([Date]) = 2017 THEN [Costs] ELSE 0 END

Always when a quarter has ended the costs in Q1 2016 should be compared with the costs in Q1 2017. But when a quarter is still running it should automatically compare month.

For example:

January 2017

February 2017

March 2017

April 2017

May 2017

January, February and March should now be combined as Q1 and compared with Q1 in 2016. April and May should be compared not as quarter but as month (April 16 with April 17 and May 16 with May 17).

When I add June 2017 it should automatically compare Q1 2016 with Q1 2017 and Q2 2016 with Q2 2017.

The format should stay “date” as it should be possible to still down drill the quarters to month if wished so.

Does anyone has an Idea how to do this?

Thanks a lot for your help!

• ###### 1. Re: When quarter closed show quarter / else show month

Hi Maria,

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

1. Last month CY: if datediff('month',[Date],today())=1 then [Costs] END

2. Last month PY: if datediff('month',[Date],today())=13 then [Costs] END

3. Last Quarter CY: if datediff('quarter',[Date],today())=1 then [Costs] END

4. Last Quarter PY: if datediff('quarter',[Date],today())=5 then [Costs] END

5. Indicator:

if datediff('month',[Date],today())=1 then "Last Month CY"

elseif datediff('month',[Date],today())=13 then "Last Month PY"

elseif datediff('quarter',[Date],today())=1 then "Last Quarter CY"

elseif  datediff('quarter',[Date],today())=5 then "Last Quarter PY"  END

Regards,

Norbert

1 of 1 people found this helpful
• ###### 2. Re: When quarter closed show quarter / else show month

Hi Norbert

Thank you very much for your help!

Your approach was very useful. But when the year goes on it would get more and more complicated as there would be more measure values.

You brought me to the idea, though, to do a calculated field with all possible outcomes called “Month 1-12 / Quarter 13-16”, just as you did it with your field “indicator”.

This made it possible to finally group all dates as needed. Now the table groups automatically quarter by quarter and next to it month by month.

With a field “Transformation Quarter / Month” I gave the before defined groups respective names. (Q1, April, Mai) ect.

The Parameter “Reference Month” gives the possibility to go exactly one year back from the chosen month.

You find my approach attached, as the formulas got quite long.

Best regards,

Maria

• ###### 3. Re: When quarter closed show quarter / else show month

Hi Maria,

My feedback is "two-folded".

b. I don't understand your remark "But when the year goes on it would get more and more complicated as there would be more measure values"

The view of my approach will always be the same "Current Month CY vs Current Month PY" and "Last full Quarter CY vs Last full Quarter PY"

If you need further explanation please let me know on return.

Regards,

Norbert