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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.