SAC Quarter to Date (QTD) calculation

[navendut]

Hello SAC Gurus,

How to calculate QTD (quarter-to-date) in SAC? For example, if the user selects May 2022, the report will show the QTD value (sum of May and June 2022).

Thank you in advance.

NT

[N1kh1l · Accepted solution]

navendut

In the new model you can create a calculated measure for QTD with below formula. Adjust as per your date dimension and Measure Name. The below is QTD for Measure [Flow_Total_FXVariable]

ITERATE(IF([d/Date].[p/MONTHDESC]=("Jan" OR "Apr"OR "Jul" OR"Oct") ,0 , PRIOR () ) + [Flow_Total_FXVariable],[Flow_Total_FXVariable], [d/Date])

Output:

Hope this helps !!

Please upvote/accept if this helps.

Nikhil

[navendut · Accepted solution]

Thank you, Nikhil. Its working as expected.

[navendut · Accepted solution]

Hello Nikhil,

How can we add a quarter in the YTD calculation, so we can get the correct number in quarters?

When we are using hierarchy in the Date (time Dimension),

Thank you in advance

NT

[N1kh1l · Accepted solution]

navendut

I am not sure what you mean by adding Quarters to YTD calculation. in my above post there is hierrachy Q1 which shows same value as Q1 QTD. May be put up an example of what you mean.

Nikhil

[navendut · Accepted solution]

Hello Nikhil,

I am trying the hierarchy view for YTD calculation using the following calculated measure formula. However, I am getting the correct number in the flat presentation (base level), but in the hierarchy view, QTD numbers are not rolling for YTD calculation. Screenshot attached

Calculcated Measure YTD: ITERATE(IF([d/Date].[p/MONTHDESC]=("Jan" ) ,0 , PRIOR () ) + [SIGNDATA],[SIGNDATA], [d/Date])

I appreciate any help you can provide.

NT

[mayuri_ghosh]

Hi,'

You can calculate QTD by using restricted measure and specifying the input control -

Not only QTD you can calculate any date calculation like YTD,MTD using restricted measure.

Thank you.

[amartya_ghosh2023]

Hi.

Go to the restricted measure and try this. You will get the QTD value

[navendut]

Hello Amartya, your solution is also working.

Thank you again for your help.

NT