Forum Discussion

igorabdo's avatar
igorabdo
Advocate II
7 years ago
Solved

Repeat last value

Hi folks

 

I've a question.

For example.

If the number is blank, return the last data that is no blank.
For example.
In October, I don't have the value, so, bring the last that isn't blank. 

 



Best regards

Need Help
Show and Tell
  • LivioLanzo's avatar
    LivioLanzo
    7 years ago

    Hello igorabdo

     

    I have normalized come columns and created the above model:

     

     

    Then I have used the below measure to calculate the figures:

     

     

    Avg Cost Last Non Blank =
    SUMX (
        Periods,
        VAR curPer = Periods[DATA_INICIO]
        RETURN
            IF (
                ISEMPTY ( RELATEDTABLE ( data ) ),
                CALCULATE (
                    SUM ( data[CUSTO_MEDIO] ),
                    LASTNONBLANK (
                        CALCULATETABLE ( Periods, Periods[DATA_INICIO] <= curPer ),
                        COUNTROWS ( RELATEDTABLE ( data ) )
                    )
                ),
                CALCULATE ( SUM ( data[CUSTO_MEDIO] ) )
            )
    )

     

    Which is yielding the below results  ( I have conditionally formatted in red the figures that are calculated using the LASTNONBLANK part )

     

     

     

    I am not able to upload the file at the moment but if you still have doubts I will be able to upload it later in the day. 

7 Replies

      • LivioLanzo's avatar
        LivioLanzo
        Solution Sage
        7 years ago

        Hello igorabdo

         

        I have normalized come columns and created the above model:

         

         

        Then I have used the below measure to calculate the figures:

         

         

        Avg Cost Last Non Blank =
        SUMX (
            Periods,
            VAR curPer = Periods[DATA_INICIO]
            RETURN
                IF (
                    ISEMPTY ( RELATEDTABLE ( data ) ),
                    CALCULATE (
                        SUM ( data[CUSTO_MEDIO] ),
                        LASTNONBLANK (
                            CALCULATETABLE ( Periods, Periods[DATA_INICIO] <= curPer ),
                            COUNTROWS ( RELATEDTABLE ( data ) )
                        )
                    ),
                    CALCULATE ( SUM ( data[CUSTO_MEDIO] ) )
                )
        )

         

        Which is yielding the below results  ( I have conditionally formatted in red the figures that are calculated using the LASTNONBLANK part )

         

         

         

        I am not able to upload the file at the moment but if you still have doubts I will be able to upload it later in the day.