Error after Adding blank in a measure

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

feb 23000 12000

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

Forum Discussion