Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Nullify values after last YTD observation

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

Need Help

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        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