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....
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. 

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. 

AnnaW's avatar
AnnaW
Regular Visitor
4 years ago

Hi LivioLanzo, thanks very much for your solution, it works great for me on the lowest level of data (PK_ITEM in your example) - but when I remove the items from a table visual and want to have a total per month then the numbers don't add up properly, would you know how to fix it?