Request for DAX Measure to Track Closed and Extended Cases Across Reporting Periods

[Srav1234]

Hi Team,
Need your help in one requirement .Can any please look into this and help me here.

I have a dataset with the columns: CauseID, ResponseStatus and PlannedCompletionDt. When a reporting period (e.g., April 2024) is selected in a slicer, I need to calculate counts for two categories: Closed and Extension.

For the Closed category:

1. Identify all records within the selected month (April 2024) where ResponseStatus is "Open" and PlannedCompletionDt falls within the start and end of the selected month.
2. Track these CauseIDs into the next reporting period (May 2024). If the ResponseStatus for these CauseIDs changes to "Closed" within May 2024, count these instances.

For example, if three CauseIDs are "Open" in April 2024 and two of these are marked as "Closed" in May 2024, the count for Closed would be 2.

For the Extension category:

1. From the same set of records identified in April 2024 as "Open," check the next period (May 2024).
2. Count how many of these have a ResponseStatus still "Open" with a PlannedCompletionDt that extends beyond May 2024.

For instance, if three CauseIDs are "Open" in April 2024 and one of these remains "Open" in May 2024 with a PlannedCompletionDt beyond May, the count for Extension would be 1.

So finally if i select April Month is slicer I want  closed count as 2 and Extension as 1.
I appreciate any guidance or solutions you can provide to help implement this functionality.
Thanks

Sravan

[Srav1234]

@amitchandak @lbendlin @Kishore_KVN @Ashish_Mathur can anyone of you please help in this. 

[Anonymous]

Hi @Srav1234 ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Srav1234]

I want to create card visuals to display the count of closed and extension items. Specifically, I need one card visual to show the count of closed items and another card visual to show the count of extension items.

[Srav1234]

@amitchandak @lbendlin @Kishore_KVN @Ashish_Mathur can anyone of you please help in this. Its a bit urgent requirement.