Using DAX to get a counted expression grouped by quarter

[nericksx]

Suppose acedemic workflows, in different subjects, are perioically tested & retested. You can see in the dataset below: the subject, the workflow name, the quarter it was tested & the score. I have been able to create measures that return the previous test quarter and score (see the variable in the code), if the workflow has been tested more than once. What I need is to count up all the workflows that have improved (i.e. the most recent score is higher than the last score).

The exact table visual that I would expect is:

Year Quarter	Count of Workflows	Count of Improved Workflows
2021 Q2	5
2021 Q3	3
2021 Q4	4
2022 Q1	11	5
2022 Q2	2	2

I'm getting is a visual that looks like:

Year Quarter	Count of Workflows	Count of Improved Workflows
2021 Q2	5
2021 Q3	3
2021 Q4	4	4
2022 Q1	11	11
2022 Q2	2

As you can see, it is aggregating by quarter, but not in the way I would expect (if I add the "subject" column, it aggregates correctly, but I don't *want* the subject column there).
The Data:
Scores By Workflow =

Product	Workflow	Workflow Score Avg	Year Quarter
Math	Chapter Test	6.38	2022 Q1
Math	Chapter Test	5.67	2021 Q2
Math	Register Account	6.50	2021 Q4
Math	Create Report	7.00	2022 Q1
Math	Create Report	6.50	2021 Q2
Math	Get Help	6.25	2021 Q4
Math	Get Help (TSA)	4.50	2021 Q4
Math	Create Graph	6.90	2022 Q1
Math	Create Graph	6.63	2021 Q2
Math	Corrections	7.00	2022 Q1
Math	Corrections	6.60	2021 Q2
Math	Chapter Review	6.50	2022 Q1
Math	Chapter Review	6.00	2021 Q2
Science	Identify Variables	5.33	2022 Q2
Science	Communicate VM Risk	6.08	2022 Q2
Science	Get Help	6.00	2021 Q3
Science	Identify Variables	4.35	2021 Q3
English	Get Help	4.67	2022 Q1
English	Biography	6.40	2022 Q1
English	Complete Reviews	6.25	2022 Q1
English	Editing	5.10	2022 Q1
Math	Sorting	4.00	2021 Q4
Science	Communication	3.20	2021 Q3
English	Chapter Test	6.00	2022 Q1
English	Group Work	5.80	2022 Q1

The code:

VAR _mostRecentTestDate = 
     MAX( 'Scores by Workflow'[Year Quarter] )

VAR _mostRecentScore = 
     AVERAGE('Scores by Workflow'[Workflow Score Avg]) 
   
VAR _prevTestDate = 
     CALCULATE( 
          MAX( 'Scores by Workflow'[Year Quarter] ), 
          'Scores by Workflow'[Year Quarter] < _mostRecentTestDate 
     )

VAR _prevScore = 
     CALCULATE( 
          AVERAGE('Scores by Workflow'[Workflow Score Avg]), 
          'Scores by Workflow'[Year Quarter] = _prevTestDate 
     )

VAR _improvedCount = 
    IF(
        NOT(ISBLANK(_prevTestDate)),
        CALCULATE(
            COUNT('Scores by Workflow'[Workflow]),
            FILTER('Scores by Workflow',_mostRecentScore > _prevScore)  
        )
    )
    
RETURN
_improvedCount 

Other info:

I have a date table that has a 1:many relationship with the Scores By Workflow table.

[nericksx]

Good morning, @Anonymous  thank you for trying to help with this!
I'm not looking for a comparison of an average or max score for quarters or years. I'm looking to see individual workflow score improvement from the previous testing session, which most likley won't be the last quarter. Check out this table (note - since there are multiple tasks within the same workflow during a testing session, workflow scores are averaged within a subject and quarter):

Subject	Year Quarter	Workflow Score Avg	Prev Workflow Score Avg	Prev Test Date	Workflow
Math	2022 Q1	6.38	5.67	2021 Q2	Chapter Test
Math	2022 Q1	7	6.5	2021 Q2	Create Report
Math	2022 Q1	6.9	6.63	2021 Q2	Create Graph
Math	2022 Q1	7	6.6	2021 Q2	Corrections
Math	2022 Q1	6.5	6	2021 Q2	Chapter Review
Science	2022 Q2	6.08	3.2	2021 Q3	Communicate VM Risk
Science	2022 Q2	5.33	4.35	2021 Q3	Identify Variables

These are the only subjects and workflows that have been retested. Every one of them improved. I'd like to count how many improved per quarter *only if they've been retested*. Based on the table above I would expect a count of 5 for 2022 Q1, and 2 for 2022 Q2 (see my first example table at the top). Any result that has a count in 2021 Q4 I know is not what I want, because there were no restests done in 2021 Q4.

[Anonymous]

Hi @nericksx ,

I create two measure for Maximum and Average

Count of Improved Workflows by Avg Score =
VAR cur_date =
    SELECTEDVALUE ( 'Scores By Workflow'[Year Quarter] )
VAR tmp1 =
    FILTER ( ALL ( 'Scores By Workflow' ), [Year Quarter] < cur_date )
VAR pre_date =
    MAXX ( tmp1, [Year Quarter] )
VAR tmp2 =
    FILTER ( ALL ( 'Scores By Workflow' ), [Year Quarter] = pre_date )
VAR pre_score =
    AVERAGEX ( tmp2, [Workflow Score Avg] )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Scores By Workflow'[Workflow] ),
        FILTER ( 'Scores By Workflow', [Workflow Score Avg] > pre_score )
    )
VAR ctn =
    COUNTROWS ( tmp3 )
RETURN
    IF ( ISEMPTY ( tmp2 ), BLANK (), ctn )

Count of Improved Workflows by Max Score =
VAR cur_date =
    SELECTEDVALUE ( 'Scores By Workflow'[Year Quarter] )
VAR tmp1 =
    FILTER ( ALL ( 'Scores By Workflow' ), [Year Quarter] < cur_date )
VAR pre_date =
    MAXX ( tmp1, [Year Quarter] )
VAR tmp2 =
    FILTER ( ALL ( 'Scores By Workflow' ), [Year Quarter] = pre_date )
VAR pre_score =
    MAXX ( tmp2, [Workflow Score Avg] )
VAR tmp3 =
    CALCULATETABLE (
        VALUES ( 'Scores By Workflow'[Workflow] ),
        FILTER ( 'Scores By Workflow', [Workflow Score Avg] > pre_score )
    )
VAR ctn =
    COUNTROWS ( tmp3 )
RETURN
    IF ( ISEMPTY ( tmp2 ), BLANK (), ctn )

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @nericksx ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a calculate logic.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[nericksx]

Good evening. I've edited my original question to provide more information. Does that help?

[Anonymous]

Hi @nericksx ,

"the most recent score is higher than the last score" means current Year Quarte Workflow Score Avg is higher than the previous Year Quarte Workflow Score Avg ?  Whether this comparison is the average value of the base and the previous Year Quarte or the maximum value of the previous Year Quarte ?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu