Last Result based on Date Hierarchy

Anonymous — Wed, 08 May 2019 20:33:33 GMT

Hi ,

Need some help in getting last Date based on dates only removing all other filters

Sample Date table

Year Period Status Date
2019 1 X 1/1/2019

2019 1 X 1/31/2019
2019 2 X 2/28/2019

2019 3 X 3/31/2019

2019 4 A 4/1/2019

2019 4 X 4/5/2019

Desired Output at period level

Year Period Status Date

2019 1 X 1/31/2019
2019 2 X 2/28/2019

2019 3 X 3/31/2019

2019 4 X 4/5/2019

I am getting this result if i don't add status column but as soon as i add status column its giving output at period level based on status too

Year Period Status Date

2019 1 X 1/31/2019
2019 2 X 2/28/2019

2019 3 X 3/31/2019

2019 4 A 4/1/2019

2019 4 X 4/5/2019

Thanks

Re: Last Result based on Date Hierarchy

technolog — Sat, 03 Feb 2024 17:56:33 GMT

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])
)

topic Last Result based on Date Hierarchy in DAX Commands and Tips

Last Result based on Date Hierarchy

Re: Last Result based on Date Hierarchy