Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I was wondering if someone would be able to assist with a KPI solution I'm trying to put together.
I'm wanting to have a KPI that displays the data for whichever Quarter we are currently in and one that shows the +/- difference between the current and previous QTR.
For example, based on todays date, the first KPI would show 90% and the second would show a +25%.
FY | QTR | FYQTR | QTRStart | QTREnd |
---|---|---|---|---|
FY17 | Q1 | FY17Q1 | 1/1/2017 | 3/31/2017 |
FY17 | Q2 | FY17Q2 | 4/1/2017 | 6/30/2017 |
FY17 | Q3 | FY17Q3 | 7/1/2017 | 9/30/2017 |
FY17 | Q4 | FY17Q4 | 10/1/2017 | 12/31/2017 |
FYQTR | Score |
---|---|
FY17Q1 | 65% |
FY17Q2 | 90% |
may be like this
CurrentQtr
sum({<FYQTR={"$(='FY'&Date(max(Date),'YY')&'Q'&ceil(month(max(Date))/3) )"}>}SCORE)
+/- difference between the current and previous QTR
sum({<FYQTR={"$(='FY'&Date(max(Date),'YY')&'Q'&ceil(month(max(Date))/3) )"}>}SCORE)
-
sum({<FYQTR={"$(='FY'&Date(QuarterStart(max(Date),-1),'YY')&'Q'&ceil(month(QuarterStart(Max(Date),-1))/3))"}>}SCORE)
Take a look at the function InQuarterToDate(), it could be handy in your case.