Last Result based on Date Hierarchy

Question

Hi ,Need some help in getting last Date based on dates only&nbsp; removing all other filters&nbsp;Sample Date tableYear&nbsp; &nbsp; &nbsp; &nbsp;Period&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/1/2019&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/31/2019&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/28/2019&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/31/2019&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/1/2019&nbsp; &nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/5/2019&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;Desired Output&nbsp; at period levelYear&nbsp; &nbsp; &nbsp; &nbsp;Period&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/31/2019&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/28/2019&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/31/2019&nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/5/2019&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;I am getting this result if i don't add status column but&nbsp;as soon as i add status column its giving&nbsp; output&nbsp; at period level based&nbsp; on status tooYear&nbsp; &nbsp; &nbsp; &nbsp;Period&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Status&nbsp; &nbsp; &nbsp; &nbsp;Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;&nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/31/2019&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/28/2019&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/31/2019&nbsp; &nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; A&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/1/2019&nbsp; &nbsp;2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; X&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/5/2019&nbsp;&nbsp;Thanks&nbsp;

technolog · Accepted Answer

Hello,
To address your issue in Power BI where the addition of the 'Status' column is altering the expected output, you need to create a measure that will return the last date in each period without being influenced by the 'Status' column. This can be achieved by using DAX functions that ignore the 'Status' filter.
Here is a step-by-step guide to creating the desired measure:
1. Create a New Measure: Go to your Power BI report, select the table where your date data resides, and create a new measure. This can be done by right-clicking on the table in the fields pane and selecting 'New measure'.
2. DAX Formula for the Measure:You can use the following DAX formula to create a measure that will return the last date in each period, regardless of the 'Status' column:
LastDateIgnoringStatus = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table', 'Table'[Year], 'Table'[Period]))

Forum Discussion

Last Result based on Date Hierarchy

1 Reply

Related content

Date hierarchy

Dynamic Hierarchy Slicer based on RLS

Date Hierarchy

Download cypher results

Hierarchy bar chart based on measures