Check per Quarter number if id's that fall within

[DennieM]

i have two tables which are not related to each other:

Calendar
items

Calendar

Quarter || Index

Q1 - 2024 | 1

Q1 - 2024 | 2

Q1 - 2024 | 3

Items

ID | Created | Closed
1 | Q1 - 2024 | Q1 - 2024

2 | Q1 - 2024 | Q2 - 2024

I am looking for a way to check (count) per Quarter how much id's were open. In Q1 - 2024, i had two items (1 & 2). In Q2 -2024 i had 1 item (2). etc

result should be

Quarter | Items Open
Q1 - 2024 | 2

Q2 - 2024 | 1

Q3 - 2024 | 0

Q4 - 2024 | 0

i have tried multiple things with calculate, count, etc but i am now in a write block and have no idea to get this working.

can someone point me in the right direction?

[Kedar_Pande]

@DennieM 
Make sure your Calendar table has a unique list of quarters, which it seems you already have.

Create the measure:

Items Open = 
VAR CurrentQuarter = SELECTEDVALUE(Calendar[Quarter])
VAR StartDate = 
CALCULATE(
MIN(Items[Created]),
FILTER(Items, Items[Created] = CurrentQuarter)
)
VAR EndDate = 
CALCULATE(
MAX(Items[Closed]),
FILTER(Items, Items[Closed] = CurrentQuarter)
)
RETURN 
CALCULATE(
COUNTROWS(Items),
Items[Created] = CurrentQuarter && 
(ISBLANK(EndDate) || EndDate > CurrentQuarter)
)

Add a table visualization to your report.
Include the Quarter column from the Calendar table.
Add the Items Open measure you just created.

[DennieM]

i think i am looking for something like this but;

RETURN 
CALCULATE(
COUNTROWS(Items),
Items[Created] = CurrentQuarter && 
(ISBLANK(EndDate) || EndDate > CurrentQuarter)
)

for this part, if i tweak the filter to become 

Items[Created] <= CurrentQuarter &&
(ISBLANK(EndDate) || EndDate > CurrentQuarter)

i get a lot of results (factor 150 compared to your intial filter)

[ryan_mayu]

@DennieM 

Is the Q1 Q2 in your real data? Just want to confirm if there is any date column in your calendar table. If yes, pls provide some sample data.

[DennieM]

hi,

there are actually two dates in my source table "Created_date" & "Closure_date". Both of them are date/time fields from where i extracted the Quarter&Year

e.g.

ItemId, Created_date, Closure_date

1, 1-1-2024, 31-3-2024

2, 1-4-2024, 1-8-2024

3, 31-07-2023, null

the null value means that it has not been closed yet and therefore should be added as +1 to all quarters from Q3-2023 onwards

[ryan_mayu · Accepted solution]

@DennieM 

pls try this

Measure = CALCULATE(COUNTROWS('Table'),'Table'[created_date]<=MAX('date'[Date])&&('Table'[closure_date]>=MAX('date'[Date])||ISBLANK('Table'[closure_date])))

pls see the attachment below

[Irwan]

hello @DennieM 

if you can make a new table, then try this DAX in new table (no relationship between 'Calendar' and 'Items" tabl).

Summarize =
SUMMARIZE(
    'Calendar',
    'Calendar'[Quarter],
    "Items Open",
    COUNTX(
        FILTER(
            'Items',
            'Items'[Created]=[Quarter]
        ),
        'Items'[ID]
    )
)

i am not sure how to achive your desired result based on your sample data, "Q2 - 2024" doesnt look like to have any item opens in your sample data and no "Q2 - 2024" in your 'Calendar' table.

however, the idea to achive this is, 

1. summarize your 'Calendar' table since you want to check count for every Quarter.

2. calculate countrow ID that has same Quarter value in 'Items' table and Quarter value in new table (i use 'Summarize" in this case).

Hope this will help.

Thank you.