Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
)
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])
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
42 | |
33 | |
30 | |
18 | |
18 |