Need help with DAX to fetch the last value from a column

[Anonymous]

Dear Experts

I have a table in the PBI which shows the sales % for the products week wise. I need a column 4 to display the same sales % from the 3 column if any value exist else display the last value for same product for the rest of the week.

Please can someone help me achieve this using DAX.

Thanks for your help
Mahad

[PaulDBrown · Accepted solution]

With this model:

And this measure:

Fill =
VAR __MXYearWeekbyProduct =
    CALCULATE (
        MAX ( 'Dim YearWeek'[dYearWeek] ),
        FILTER (
            ALLEXCEPT ( FTable, 'Dim Product'[dProduct] ),
            NOT ( ISBLANK ( [% Measure] ) )
        )
    )
VAR _MXbyProduct =
    CALCULATE (
        [% Measure],
        FILTER (
            ALL ( 'Dim YearWeek'[dYearWeek] ),
            'Dim YearWeek'[dYearWeek] = __MXYearWeekbyProduct
        ),
        ALLEXCEPT ( FTable, 'Dim Product'[dProduct] )
    )
VAR _Result =
    IF (
        MAX ( 'Dim YearWeek'[dYearWeek] ) < __MXYearWeekbyProduct,
        [% Measure],
        _MXbyProduct
    )
RETURN
    _Result

I've attached the sample PBIX file

[Anonymous]

Dear Paul Brown

Thank you for your efforts in providing me the solution. 

My sales % is a measure and my report has fields combination of header and line item table along with few more table from other source. Hence I need a DAX code to create a measure (not column). Please can you let me know for measure.

Thanks

Mahad

[PaulDBrown]

The code I posted is for a measure. If you still have problems please post sample data or a link to a PBIX (non-confidential)

[Anonymous]

Dear PaulDBrown

Thank you very much for the code and take effort to provide me solution. You are a star.

It's a big relief for me.

Thanks

Mahad

[parry2k]

@Anonymous two things:

- explain what you have tried so far and what kind of result did you get?

- also share your data model

- preferably share pbix file, and remove sensitive information before sharing.

It is not just simply a measure to solve the issue there could be many other reasons why it is not working. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Hi Parry

My requirement is straight forward I want a DAX code to populate the last value in the next measure column.

Please can you help me with the code.

Thanks

Mahad

[parry2k]

@Anonymous check this video on my YouTube channel Avoid pitfalls when using LASTNONBLANKVALUE and LASTNONBLANK functions - leads to incorrect results - YouTube

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[parry2k]

@Anonymous Sales % is a measure or a column in your table?

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

[Anonymous]

Hi All

Can someone please let me know if this solution is possible in PBI. I have been looking to complete my report for more than 2 weeks now. Tried many videos and blog not sure if this solution is poissble in PBI. Or please advise which site I can refer to.

Appreciate your valuable input.

Thanks

Mahad

[Anonymous]

@parry2k 

Sales % is a measure. 

Thanks

Mahad