Consecutive Quarter Index by User Type & ID

[gopokes0914]

Hi Everyone,

I'm working on a problem that I cannot develop the intended solution for. The problem has to do with remediating exceptions to a business rule, where the Parent ID is unique for an exception and then Child ID reflects each update across the exception timeframe until closure. 

There is a field for user category type (type a & b) and I need to make sure users of each type make an update each quarter the exception is open (if an exception spans 2 quarters, I expect 4 Child ID's, one by user type by quarter elapsed). I am translating the subject matter a bit here so let me know if I leave any relevant fields out in this example. 

Here are the relevant fields:

>Parent ID
>Child ID
>Status Update (Date)
>Status Update Fiscal Year & Quarter (e.g., FY23Q3)

>Elapsed Quarts for Parent ID (e.g., 6)
>Distinct Quarter for which Update was Made (e.g., 4)

>User category (a or b)
>Fiscal Year Quarter Index (This should have consecutive values with none missing, e.g., 3,4,5,6,7,8)
>Update Comments (Justification for making update)

>Flag for missing quarterly update

Ultimately I need two new columns (Dax columns are easier to validate at the moment), one is a flag for whether user type 'a' failed to make quarterly updates, and another for user type 'b'. Currenlty I am just able to make it work for the entire Parent ID irrespective of which user type failed to make their update.

An example of a flagged exception appears below, because it inclusively spans 6 quarters (from 3 to eight) but the distinct quarters in which updates were made is only 4.

p_id	c_id	update_date	fy_qtr	num_qtr	distinct_qtrs	user_type	fy_qtr_index	not_updated_qrtly
1	15	11/1/2021	FY22-Q2	6	4	b	3	1
1	28	5/19/2022	FY22-Q4	6	4	a	5	1
1	29	5/24/2022	FY22-Q4	6	4	a	5	1
1	35	8/12/2022	FY23-Q1	6	4	b	6	1
1	39	1/16/2023	FY23-Q3	6	4	b	8	1

Thanks for any/all help!
Ryan

[gopokes0914]

Hi Yilong,

Thank you sincerely for the quick solution.  It absolutely solved the problem, and I will mark it as such. 
I made a mistake in the translation of the actual problem to how it was represented but the approach you took guided that development, so you actually helped me solve two problems. 

Thanks again,

Ryan

[v-yilong-msft · Accepted solution]

Hi @gopokes0914 ,

I create a table as you mentioned.

Then I create two calculated columns. One for counting a, one for counting b.

UserTypeA_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserAUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "a",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserAUpdates > 0, 0, 1 )

UserTypeB_MissingUpdate =
VAR CurrentQuarter = 'Table'[fy_qtr_index]
VAR UserBUpdates =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[user_type] = "b",
        ALL ( 'Table' ),
        'Table'[fy_qtr_index] = CurrentQuarter
    )
RETURN
    IF ( UserBUpdates > 0, 0, 1 )

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.