Last Result based on Date Hierarchy

[Anonymous]

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

[technolog · Accepted solution]

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