Hi,
The issue is stated in two parts,
1) Dividing a target on quarter basis
Suppose target for the whole year is 100,000, the calculated column divides it on quartery basis, formula:
Target =
IF (MONTH (date) IN {1,2,3}, 23000/3,
IF (MONTH (date) IN {4,5,6}, 25000/3,
IF (MONTH (date) IN {7,8,9}, 25000/3,
IF (MONTH (date) IN {10,11,12}, 27000/3,0))))
This column is specified on Row level in Visualization - Matrix
2) Summing previous year amount and substituting $0 in place of blanks
A calculated measure formula:
PY = IF(ISBLANK(CALCULATE(SUM(amount)), SAMEPERIODLASTYEAR(date)),0,CALCULATE(SUM(amount)), SAMEPERIODLASTYEAR(date))))
This measure is placed in Values
-----------------------------------------------------------------------------------------------------------------------------------------------
However, the measure calculates extra $0 and makes the months repeat itself thrice, one with the actual amount, and others wuth $0
jan 23000 12000
jan 25000 0
jan 25000 0
feb 23000 12000
feb 25000 0
feb 25000 0
whereas it should show:
jan 23000 12000
feb 23000 6000
mar 23000 700
apr 25000 30000
and so on
------------------------------------------------------------------------------------------------------------------------------------------
when i divide the target equally by 12, this issue does not arise.
please help. Thank you in advance
