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.
I'm trying to make an output that is looking for the latest date below is my data, there are names that have multiple image and I need the image with te latest date
ID | Name | IMAGE | Price | IMAGE OUTPUT | |
1 | A | 6/1/2021 | BIKE | BIKE | |
1 | A | 6/1/2020 | GOAT | BIKE | |
1 | A | 6/1/2022 | BIKE | 7750 | BIKE |
2 | B | 1/1/2020 | TAXI | TAXI | |
2 | B | 6/1/2022 | TAXI | 150 | TAXI |
3 | C | 1/1/2020 | Notes | Cycle | |
3 | C | 8/1/2019 | Notes | Cycle | |
3 | C | 6/1/2022 | Cycle | 800 | Cycle |
3 | C | 6/1/2020 | Cycle | Cycle |
Solved! Go to Solution.
Hi @dreyes6 ,
Please try this:
IMAGE OUTPUT =
VAR _latest_date =
CALCULATE (
MAX ( 'Table'[Effective Date] ),
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
)
VAR _output =
CALCULATE (
MAX ( 'Table'[IMG] ),
FILTER (
'Table',
'Table'[Effective Date] = _latest_date
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
)
RETURN
_output
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
@dreyes6 , you can use a table visual to display the latest date for each IMAGE. Just drag the column IMAGE and your Date column into a table visual. Your sample data has no name for column 3 so I'm calling it Column3.
Then click the little Down arrow to the right of Column3 and select Latest
With your sample data this gives me:
Is this what you are after?
I think what you are looking for is to have a new column in your table that indicates whether the row is the latest row for that image. For example a column called IsLatest which is either Y or N. Then your reports can simply filter for IsLatest = Y. I notice in your sample data that the Price column only seems to have a value for the latest rows for each image. Is it possible that when the Price field is not blank then that row is the latest for that image?
actually the data is like this,
ID | Name | Effective Date | IMG |
1 | A | 6/1/2021 | BIKE |
1 | A | 6/1/2020 | GOAT |
1 | A | 6/1/2022 | BIKE |
2 | B | 1/1/2020 | TAXI |
2 | B | 6/1/2022 | TAXI |
3 | C | 1/1/2020 | Notes |
3 | C | 8/1/2019 | Notes |
3 | C | 6/1/2022 | Cycle |
3 | C | 6/1/2020 | Cycle |
and I wan to have another calculated column that will show complete data for IMG, so the output is like this
ID | Name | Effective Date | IMG | IMG OUTPUT |
1 | A | 6/1/2021 | BIKE | BIKE |
1 | A | 6/1/2020 | GOAT | BIKE |
1 | A | 6/1/2022 | BIKE | BIKE |
2 | B | 1/1/2020 | TAXI | TAXI |
2 | B | 6/1/2022 | TAXI | TAXI |
3 | C | 1/1/2020 | Notes | Cycle |
3 | C | 8/1/2019 | Notes | Cycle |
3 | C | 6/1/2022 | Cycle | Cycle |
3 | C | 6/1/2020 | Cycle | Cycle |
Hi @dreyes6 ,
Please try this:
IMAGE OUTPUT =
VAR _latest_date =
CALCULATE (
MAX ( 'Table'[Effective Date] ),
FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) )
)
VAR _output =
CALCULATE (
MAX ( 'Table'[IMG] ),
FILTER (
'Table',
'Table'[Effective Date] = _latest_date
&& 'Table'[ID] = EARLIER ( 'Table'[ID] )
)
)
RETURN
_output
Best Regards,
Gao
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
My Bad, here you go thank u
ID | Name | Effective Date | IMG | Price | IMG OUTPUT |
1 | A | 6/1/2021 | BIKE | BIKE | |
1 | A | 6/1/2020 | GOAT | BIKE | |
1 | A | 6/1/2022 | BIKE | 7750 | BIKE |
2 | B | 1/1/2020 | TAXI | TAXI | |
2 | B | 6/1/2022 | TAXI | 150 | TAXI |
3 | C | 1/1/2020 | Notes | Cycle | |
3 | C | 8/1/2019 | Notes | Cycle | |
3 | C | 6/1/2022 | Cycle | 800 | Cycle |
3 | C | 6/1/2020 | Cycle | Cycle |
Appreciate anyone's help. Thanks a lot community
Here is how you can add the IsLastest column:
Create a Calculated Column
IsLatest =
VAR vImage = YourTable[IMAGE]
VAR vLastDate = CALCULATE(MAX('YourTable'[Column3]), ALL(YourTable), YourTable[IMAGE] = vImage)
RETURN
IF('YourTable'[Column3] = vLastDate, "Y", "N")
Then you can use a table visual to display all the data. Make sure you set all the fields to "Don't Summarize". This gives:
You can then simply filter the IsLatest column to Y, which makes the table display this:
Is this what you are looking for?
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |