Repeat last value

[igorabdo]

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

[LivioLanzo · Accepted solution]

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]

Check out the LASTNONBLANK formula: https://www.sqlbi.com/articles/semi-additive-measures-in-dax/

[igorabdo]

No. 
I try but not sucess
Example

[LivioLanzo · Accepted solution]

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]

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?

[igorabdo]

Thanks.
Please, share you pbix.

Best regards.

[LivioLanzo · Accepted solution]

@igorabdo

You can download it here: https://1drv.ms/u/s!AiiWkkwHZChHjxV0PpsCKUIBzJVu

[igorabdo]

Thanks very much @LivioLanzo