Get latest status based on same identifier

[IkmalFikri900]

Hi everyone, I want to develop this, basically, it will display status based on the latest status accoridng to the Linked column..if the user did not select any specific quarter/year...but if they select Q1 2025, the status will be displayed as Ongoing. How can i do this?

[v-csrikanth]

Hi @IkmalFikri900 

It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @IkmalFikri900 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.
Thank you.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth · Accepted solution]

HI @IkmalFikri900 
The file shared by @Ashish_Excel  provides accurate results as per the request.
To display 200 in your card, I would recommend using Latest measure where it will correctly display the expected value.
See below image for reference-

 
Reuploadiing the pbix for reference.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @IkmalFikri900 

I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

Best Regards,
Community Support Team _ C Srikanth.

[IkmalFikri900]

Hi, apologies for the reply,
I still havent' been able to resolved the issues.

ID	LinkedID	Date	StatusUpdate	Values	IssueStatus
1	1	1-Jan-25	Update 1	100	Ongoing
2	1	2-Feb-25	Update 2	150	Completed
3	3	1-Jan-25	Update 3	200	Ongoing
4	3	2-Feb-25	Update 4	200	Ongoing

So, the expectation is, when User filter ALL Date Column, visuals should display 1 Ongoing and 1 Completed

While if the user select January, it should be 2 Ongoing case 0 Completed

The issue is i can use Measure but i want to use card to visualize the number of case and values where i can filter using just Status.
So i have to use calculated column where the expected results is:

When no filter is selected:

ID	LinkedID	Date	StatusUpdate	Values	IssueStatus	Latest Status
1	1	1-Jan-25	Update 1	100	Ongoing	Completed
2	1	2-Feb-25	Update 2	150	Completed	Completed
3	3	1-Jan-25	Update 3	200	Ongoing	Ongoing
4	3	2-Feb-25	Update 4	200	Ongoing	Ongoing

But when January is Selected (The table is filtered)

ID	LinkedID	Date	StatusUpdate	Values	IssueStatus	Latest Status
1	1	1-Jan-25	Update 1	100	Ongoing	Ongoing
3	3	1-Jan-25	Update 3	200	Ongoing	Ongoing

Is this possible?

[IkmalFikri900]

Update
Sample Data:

ID	LinkedID	Date	StatusUpdate	Values	IssueStatus
1	1	1-Jan-25	Update 1	100	Ongoing
2	1	2-Feb-25	Update 2	150	Completed
3	3	1-Jan-25	Update 3	200	Ongoing
4	3	2-Feb-25	Update 4	200	Ongoing

So, the expectation is, when User filter ALL Date Column, visuals should display this

Info
Ongoing Cases	1
Completed Cases	1
Values	350

Since for the latest item (ie February), the latest IssueStatus is Completed and Latest Value is 150 for LinkedID 1. but LinkedID 2 stays the same

When the user Filter January 2025 only, visuals should display:

Info
Ongoing Cases	2
Completed Cases	0
Values	300

Since in January, both LinkedID 1 and 2 status is Ongoing and the value is 100+200

[Ashish_Excel]

Hi,

PBI file here.

Hope this helps.

[IkmalFikri900]

Thanks. it helps a lot,

Just the Values/T amount where if no filter is selected for ongoing, it shows 500 where it should shows 200 only

[Ashish_Excel]

You are welcome.  Cannot understand your concern.

[ryan_mayu]

@IkmalFikri900 

you can try to create a new table

Table 2 = DISTINCT('Table'[Quarter])

then create a measure

Measure = if(max('Table'[Quarter])=max('Table 2'[Quarter]),1)

add this measure to visual filter and set to 1

pls see the attachment below

[IkmalFikri900]

Hi, thanks for the suggestions, but it doesn't fit my requirement, can you assist?

[ryan_mayu]

then pls provide more details about your request. what is the expected output?

[IkmalFikri900]

Sample Data:

ID	LinkedID	Date	StatusUpdate	Values	IssueStatus
1	1	1-Jan-25	Update 1	100	Ongoing
2	1	2-Feb-25	Update 2	150	Completed
3	3	1-Jan-25	Update 3	200	Ongoing
4	3	2-Feb-25	Update 4	200	Ongoing

So, the expectation is, when User filter ALL Date Column, visuals should display this

Info
Ongoing Cases	1
Completed Cases	1
Values	350

Since for the latest item (ie February), the latest IssueStatus is Completed and Latest Value is 150 for LinkedID 1. but LinkedID 2 stays the same

When the user Filter January 2025 only, visuals should display:

Info
Ongoing Cases	2
Completed Cases	0
Values	300

Since in January, both LinkedID 1 and 2 status is Ongoing and the value is 100+200

[ryan_mayu]

@IkmalFikri900 

pls see the attachmen below

[Shravan133]

try this:

CurrentStatus :=
VAR IsFiltered =
ISFILTERED('Calendar'[Quarter]) || ISFILTERED('Calendar'[Year])

VAR SelectedLinkedDate =
IF(
IsFiltered,
MAX(ActivityTable[Linked]), -- Use the latest date in selected period
CALCULATE(
MAX(ActivityTable[Linked]),
ALL(ActivityTable) -- Ignore slicers if nothing is selected
)
)

RETURN
CALCULATE(
MAX(ActivityTable[Status]),
FILTER(
ActivityTable,
ActivityTable[Linked] = SelectedLinkedDate
)
)

[IkmalFikri900]

Using this formula, when no specific quarter/year selected, it display Ongoing instead of Completed.

Can you assist?