Fill blank values with previous value

[Anonymous]

Hi ,

I m new to power bi ,I have below dataset. In Target 2 column , blank values filled with previous value.Please help me to write dax  for same.

Year-Week	TARGET	TARGET2
2020-25	14112	14112
2020-26		14112
2020-27	10872	10872
2020-28		10872
2020-29		10872
2020-30	8928	8928

Thanks

[Fowmy]

Hi,

Try this as a new column.

VAR LNBT = 
    CALCULATE(MAX('Table'[YEAR-WEEK]),
    FILTER(
        ALL('Table'),
        'Table'[YEAR-WEEK]<=EARLIER('Table'[YEAR-WEEK]) && 'Table'[TARGET]<>BLANK()
    )
    )
RETURN
CALCULATE(
    MAX([TARGET]),
    'Table'[YEAR-WEEK] = LNBT ,ALL('Table')
)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hey,

Thanks its working. I m trying same formula in measure but its giving error.

Can you tell me reason we couldnt use this formula in measure.

Thanks

[Fowmy]

@Anonymous 

Evaluation context in Column (Table) and Measure are different in this case.

Thanks
Did I answer your question? Mark my post as a solution!

[Fowmy · Accepted solution]

@Anonymous 

You can use the below Measure, I only changed the EARLIER function to MAX.

Measure_Filldown  =
VAR LNBT = 
    CALCULATE(MAX('Table'[YEAR-WEEK]),
    FILTER(
        ALL('Table'),
        'Table'[YEAR-WEEK]<=MAX('Table'[YEAR-WEEK]) && 'Table'[TARGET]<>BLANK()
    )
    )
RETURN
CALCULATE(
    MAX([TARGET]),
    'Table'[YEAR-WEEK] = LNBT ,ALL('Table')
)

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi ,

I m getting one strange issue. I have connected DATA table with Date dimension and pulling Year-Week from date table. Its showing incorrect result when i sort measure2 based year-week order,otherwise its showing correct result.

Using below formula

Measure2 = VAR LNBT =

CALCULATE(MAX('Date'[YEAR-WEEK]),

FILTER(

ALL('Date'),

'Date'[YEAR-WEEK]<=MAX('Date'[YEAR-WEEK]) && RELATED(Data[TARGET])<>BLANK()

)

)

RETURN

CALCULATE(

MAX([TARGET]),

'DATA'[YEAR-WEEK] = LNBT ,ALL('DATE')

)

[Fowmy]

Hi @Anonymous 

I tested with the measure that I shared and it works, please check the attached file.

https://1drv.ms/u/s!AmoScH5srsIYgYFdHeVAE8V-SZzFFQ?e=d1YwV3

[Anonymous]

Hi,

I tested with your measure in my PBI,its not showing same result.

https://www.dropbox.com/s/5a0qoukpjs18rtw/TEST.pbix?dl=0

[Fowmy]

@Anonymous 

Where do you do the sorting?

The measure works on the Ascending Order of YEAR-WEEK. Also, I gave the solution based on your original requirement where you mentioned a single table with blank values. The MEASURE2 works fine, which is a modification to the measure I shared. 

[Anonymous]

Right, your measure is working absolutely correct and I have modified you're measure in change data table. Only issue is when I sort by Year-Week it's working fine but when I sort Year-Week order column then it's showing wrong result

[rajulshah]

Hello @Anonymous,

Do you want this to be resolved in Power Query or DAX function? Please let me know.

[Anonymous]

DAX Measure