Identify Most Recent Count

[JaysYee]

I'm trying to find the most recent distinct count value from a text string grouping using DAX.

For example, here is the underlying data:

Year-Quarter	Employee Name
2023-Q1	A
2023-Q1	B
2023-Q1	C
2023-Q1	D
2023-Q1	E
2023-Q2	A
2023-Q2	B
2023-Q2	C
2023-Q2	D
2023-Q3	A
2023-Q3	B
2023-Q3	C
2023-Q3	D

I would ultimately like a card that displays the count of the most recent Year-Quarter, but when applying a filter on a specific Year-Quarter the card will show that quarter's count.  For example:

Year-Quarter	Distinct Employee Count
2023-Q1	5
2023-Q2	4
2023-Q3	4

By default (or all) the card will show 4, but when I select the 2023-Q1 filter the card will show 5.

I've tried:

1. Creating a column rankx on the Year-Quarter
2. Then creating a measure where by I used "group by" to group the Year-Quarter and index (from step1) and then tried to return the last value of the grouped table.  Something like:

Recent Count =
var CountTable = GROUPBY('Employee Count',Employee Count[Year-Quarter],Employee Count[Rank],"Quarter Count",COUNTROWS(CURRENTGROUP()))
return
    LASTONBLANK(?)

I just can't figure out how to find the last value of the most recent Year-Quarter in a table created by the "group by" function.

I searched past posts, and I can't find a solution.

Thank you!

[v-yangliu-msft]

Hi  @JaysYee ,

Here are the steps you can follow：

1 . In Power Query -- Add Column – Index Column – From 1.

2. Create measure.

Measure =
var _selectYearQu=
SELECTEDVALUE('Employee Count'[Year-Quarter])
var _Category=
SELECTEDVALUE('Employee Count'[Category])
var _max=
MAXX(FILTER(ALL('Employee Count'),'Employee Count'[Index]=MAXX(ALLSELECTED('Employee Count'),[Index])),'Employee Count'[Year-Quarter])
return
IF(
    HASONEVALUE('Employee Count'[Year-Quarter]),
    CALCULATE(DISTINCTCOUNT('Employee Count'[Employee Name]),ALLSELECTED('Employee Count'))
    ,
    CALCULATE(DISTINCTCOUNT('Employee Count'[Employee Name]),FILTER(ALL('Employee Count'),
    'Employee Count'[Year-Quarter]=_max))
)

3. Result:

Best Regards,

Liu Yang

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

[v-yangliu-msft]

Hi  @JaysYee ,

Here are the steps you can follow：

1. In Power Query -- Add Column – Index Column – From 1.

2. Create measure.

Measure =
var _select=SELECTCOLUMNS('Employee Count',"select",[Year-Quarter])
var _max=
MAXX(FILTER(ALL('Employee Count'),'Employee Count'[Index]=MAXX(ALLSELECTED('Employee Count'),[Index])),[Year-Quarter])
return
IF(
    NOT(HASONEVALUE('Employee Count'[Year-Quarter])) ,   
    CALCULATE(DISTINCTCOUNT('Employee Count'[Employee Name]),FILTER(ALL('Employee Count'),'Employee Count'[Year-Quarter]=_max))
    ,
    CALCULATE(DISTINCTCOUNT('Employee Count'[Employee Name]),FILTER(ALL('Employee Count'),'Employee Count'[Year-Quarter]=_select))
)

3. Result:

By default (or all):

Select 2023-Q3:

Best Regards,

Liu Yang

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

[JaysYee]

Hi @v-yangliu-msft ,

Thank you that worked.  I'm sorry I should have included additional columns, like the category of employees.  When applying an additional filter then the card doesn't seem to reflect it.

For example:

Year-Quarter	Employee Name	Category
2023-Q1	A	Remote
2023-Q1	B	Remote
2023-Q1	C	Office
2023-Q1	D	Office
2023-Q1	E	Office
2023-Q2	A	Remote
2023-Q2	B	Remote
2023-Q2	C	Office
2023-Q2	D	Office
2023-Q3	A	Remote
2023-Q3	B	Remote
2023-Q3	C	Office
2023-Q3	D	Office

So 2023-Q1 should show 3 when applying Office filter while 2023-Q4 should show 2.

Is there a way to make it more dynamic so it will accept other filters/columns?