Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
jtooke
Helper I
Helper I

Get the latest status for an ID at the end of each period

Hi,

 

I am looking to create a new table based on a data table to summarize the data based on the status at the end of each period. The data will need to be summarized at several levels. 

 

My data looks like this. Quarter Period refers to the quarter of the ID Created field. I would want to evaluate each ID to determine the Latest Status at the end of each Quarter Period. For example, ID "D" would be counted as Open in the Current Quarter -3 group, and Closed in the Current Quarter -2 group. ID "A" would only be counted as Open in the Current Quarter group (all other statuses within the same quarter would be irrelevant).

IDStatusID CreatedID UpdatedGroupCountryQuarter Period
AOpen1/1/20241/1/2024ABCUSCurrent Quarter
BOpen2/1/20242/1/2024ABCUSCurrent Quarter
AOpen1/1/20241/8/2024ABCUSCurrent Quarter
AClosed1/1/2024########ABCUSCurrent Quarter
CClosed1/1/20234/1/2023DEFEnglandCurrent Quarter -4
DOpen6/1/20236/1/2023DEFEnglandCurrent Quarter -3
EOpen10/1/2023########DEFUSCurrent Quarter -1
BClosed2/1/2024########ABCUSCurrent Quarter
DClosed6/1/20238/1/2023DEFEnglandCurrent Quarter -3
FOpen8/1/20238/1/2023ABCEnglandCurrent Quarter -2

 

Thank you!

 

1 ACCEPTED SOLUTION
danextian
Super User
Super User

Hi @jtooke 

For example, ID "D" would be counted as Open in the Current Quarter -3 group, and Closed in the Current Quarter -2 group. ID  Using the data provicded, there is only one quarter for D and assuming that the basis for the latest status is updated date, D would be tagged as Closed for both rows. Can you please clarify this?

 

danextian_0-1707776765775.png

 

Max Status Per Quarter = 
VAR _MAX_DATE =
    CALCULATE (
        MAX ( 'Table'[ID Updated] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] )
    )
VAR __RESULT =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] ),
            'Table'[ID Updated] = _MAX_DATE
        )
    )
RETURN
    __RESULT
Max Status Per Quarter2 = 
IF (
    'Table'[Status] = 'Table'[Max Status Per Quarter],
    'Table'[Max Status Per Quarter]
)

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

2 REPLIES 2
danextian
Super User
Super User

Hi @jtooke 

For example, ID "D" would be counted as Open in the Current Quarter -3 group, and Closed in the Current Quarter -2 group. ID  Using the data provicded, there is only one quarter for D and assuming that the basis for the latest status is updated date, D would be tagged as Closed for both rows. Can you please clarify this?

 

danextian_0-1707776765775.png

 

Max Status Per Quarter = 
VAR _MAX_DATE =
    CALCULATE (
        MAX ( 'Table'[ID Updated] ),
        ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] )
    )
VAR __RESULT =
    CALCULATE (
        MAX ( 'Table'[Status] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[ID], 'Table'[Quarter Period] ),
            'Table'[ID Updated] = _MAX_DATE
        )
    )
RETURN
    __RESULT
Max Status Per Quarter2 = 
IF (
    'Table'[Status] = 'Table'[Max Status Per Quarter],
    'Table'[Max Status Per Quarter]
)

 










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Hi @danextian ,

 

Thank you for your help! The reason that D would be Open in the Current Quarter -3 group was that as of June 1, 2023, D has open status, and is not updated to closed until Aug 1, 2023. Current Quarter -3 would end on June 30, 2023, so at that time D was still open.

 

Given this problem, I have decided to push the math to a different solution rather than use Power BI, but thank you again for your reply!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.