Forum Discussion

igorabdo's avatar
igorabdo
Advocate II
7 years ago
Solved

last value with conditional (zero)

HI folks   I want to repeat the last value when the actual value is numeral zero. I tried to use with earlier, but unsuccessfully.     Tks Igor
Need Help
  • AlB's avatar
    AlB
    7 years ago

    Hi igorabdo

     

    If you want to have the average at the total there are two possibilities. I'm not sure which one you prefer.

    One is that we take the average with the original zeros (i.e. without the filled-in values). Note that we are using the [Mesure_CUSTO] defined previously. You don't need to use it in the visual any longer but do keep the definition.

     

    Measure_CUSTO_With_Avg@TotalWithOriginalZeros =
IF (
    ISFILTERED ( Table1[Mês Ano] );
    [Measure_CUSTO];
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
            "Result"; CALCULATE ( SUM ( Table1[CUSTO] ) )
        );
        [Result]
    )
)

     

     

igorabdo's avatar
igorabdo
Advocate II
7 years ago

Hi AlB

 

Example of PBIX

 

It's simple table.

I need to create a measure that, if you haven't the cost, bring the last value that isn't zero or null.

 

 

 

 

AlB's avatar
AlB
Community Champion
7 years ago

igorabdo

Not trivial. You have to use an explicit measure instead of relying on an implicit one. Create this measure and place it in the table visual:

 

Measure_CUSTO = 
VAR _LatestNonZeroDate =
    CALCULATE (
        MAX ( Table1[Mês Ano] );
        FILTER (
            ALL ( Table1[Mês Ano] );
            Table1[Mês Ano] <= SELECTEDVALUE ( Table1[Mês Ano] )
                && CALCULATE ( SUM ( Table1[CUSTO] ) ) <> 0
        )
    )
RETURN
    CALCULATE (
        SUM ( Table1[CUSTO] );
        Table1[Mês Ano] = _LatestNonZeroDate
    )

 

  • igorabdo's avatar
    igorabdo
    Advocate II
    7 years ago

    It's perfect AlB

    The measure it's amazing.

     

    The last question

    If I want an average in the final?

     

     

    But don't worry. Your measure is perfect

    • igorabdo's avatar
      igorabdo
      Advocate II
      7 years ago

      Hi AlB. Do you know what's happen?

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        Hi igorabdo

         

        If you want to have the average at the total there are two possibilities. I'm not sure which one you prefer.

        One is that we take the average with the original zeros (i.e. without the filled-in values). Note that we are using the [Mesure_CUSTO] defined previously. You don't need to use it in the visual any longer but do keep the definition.

         

        Measure_CUSTO_With_Avg@TotalWithOriginalZeros =
IF (
    ISFILTERED ( Table1[Mês Ano] );
    [Measure_CUSTO];
    AVERAGEX (
        ADDCOLUMNS (
            SUMMARIZE ( Table1; Table1[ITEMID]; Table1[Mês Ano] );
            "Result"; CALCULATE ( SUM ( Table1[CUSTO] ) )
        );
        [Result]
    )
)