Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

 

Dear Experts

Sales % all weeks.JPG

 

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

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

With this model:

model.png

 

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

result.pngI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

10 REPLIES 10
PaulDBrown
Community Champion
Community Champion

With this model:

model.png

 

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

result.pngI've attached the sample PBIX file





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

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
Super User
Super User

@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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Super User
Super User

 

@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 YouTube.gif 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!

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

parry2k
Super User
Super User

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

 

Follow us on LinkedIn and YouTube.gif 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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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
Not applicable

@parry2k 

 

Sales % is a measure. 

 

Thanks

Mahad

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.