Nullify values after last YTD observation

[Anonymous]

Dear Community,

I have made a graph showing Revenue and Budget using a YTD calculation. However, after my last observation, the YTD assumes my values to be constant and not nullifying them.

How can i make a graph that shows the actual revenue YTD as it is, but giving a value of 0 or null after my latest actual revenue available?

Here below you find the formula and screenshots.

FORMULA for YTD:

Rev. YTD Actuals = TOTALYTD(
SUM('Raw Data'[Total RevActual]);'Raw Data'[Fiscal year/period])

In the "edit query" tab

[v-cherch-msft]

Hi @Anonymous

You may try to use IF Function. For example:

Measure =
IF(SUM('Raw Data'[Total RevActual])=0,0,[Rev. YTD Actuals])

Regards,

Cherie

[Anonymous]

Hi,

It doesn't work.

Could you please insert full formula.

Current:

Rev. YTD Actuals = TOTALYTD(SUM('Raw Data'[Total RevActual]);'Raw Data'[Fiscal year/period]) 

I tried:

Rev. YTD Actuals = IF(TOTALYTD(
SUM('Raw Data'[Total RevActual]);'Raw Data'[Fiscal year/period])=0;0;TOTALYTD(SUM('Raw Data'[Total RevActual]);'Raw Data'[Fiscal year/period]))

but nothing changes.

[v-cherch-msft]

Hi @Anonymous

You may also try below measure. If it is not your case, please share a simplified data sample.

Rev. YTD Actuals = TOTALYTD(SUM('Raw Data'[Total RevActual]);'Raw Data'[Fiscal year/period]) 

Measure =
IF (
    CALCULATE (
        [Rev. YTD Actuals],
        FILTER (
            ALL ( 'Raw Data' ),
            'Raw Data'[Fiscal year/period] < MAX ( 'Raw Data'[Fiscal year/period] )
        )
    )
        = [Rev. YTD Actuals],
    0,
    [Rev. YTD Actuals]
)

Regards,

Cherie