DAX Take the last non-null value in column until the new one arrives

[vetty]

Good Morning All,

I've searched around the web but found no solution. My problem maybe is simple but, I've no idea how to solve it.

For each row in this dataset, I need to calculate the formula B/D*C and when the value of D column is Blank I want to use the last value until the new one arrives, as shown in the screenshot.

Thanks in advance 

[SpartaBI · Accepted solution]

@vetty if you want to create this column in the model itslef in DAX, create this calculated column and use it for your calculations:

D Filled = 
VAR _currrentdate = 'Table'[A]
RETURN
    CALCULATE(
        LASTNONBLANKVALUE(
            'Table'[A],
            CALCULATE(SUM('Table'[D]))
        ),
        REMOVEFILTERS(),
        'Table'[A] <= _currrentdate 
    )

[vetty]

Thank you @SpartaBI it was what I'm trying to do. Also the solution from @amitchandak  works very well. Both solutions works also when the dataset refresh, so I'm pretty curious about which has the best performance. I think that @amitchandak solution is faster because power bi calculates the formula for the column every start. Any remarks will be appreciated

[SpartaBI]

@vetty 
The advantage in the Power Query solution is that you don't have an extra column in the model.
The disadvantage in the Power Query solution is that you need to make sure the table is sorted by dates before applying those steps. Sorting is an expensive operation. 
The dax solution will work regardless if the table is sorted before you bring it to the model.
Regarding refreshes, it depends on the folding of the query generated in Power Query to the data source. Both power query transformation and calculated cloumns are part of the refresh process of a model. It's not necessary that the Power Query solution is faster than the DAX option. In case the query is not folding good to the data source it can actually be slower that using the DAX option.
To sum it all up, as usual, the answer is: "It Depends" 🙂
Hope I was clear and that it helped you. 

[vetty]

Very Clear, Thank you again 😉

[amitchandak]

@vetty , Power query fill down

Power Query - Fill Up, Fill Down : https://www.youtube.com/watch?v=fuH29kkK12A&list=PLPaNVDMhUXGbfdGuSsfPR1qKBRT5Ywlki&index=5

[vetty]

Thanks, it work like a charm!