Only count if same entity has an entry within the last year

[AmandaCarriveau]

I need to count something as having occurred only if it was done no longer than 1 year ago. For example, task A is recorded in an Excel file. Multiple departments use the file to record completion of task A, which has to be done at least once a year by each team. I need to count task A as having occurred for each team only if the team has not gone more than 1 year since they last performed the task. See example data below. I would count teams West, North and South as complete (they have done the task within 1 year of today (May 13 2024) and would NOT count team East as they have gone more than 1 year since last performing the task. How can I accomplish this in a Power BI report?

Team	Date Completed
East	10/5/2022
West	1/7/2023
North	2/3/2023
South	5/6/2023
West	12/31/2023
North	1/2/2024
South	4/1/2024

[smpa01 · Accepted solution]

@AmandaCarriveau  can you please try the following instead

Measure = 
VAR _count =
    IF (
        CONVERT (
            TODAY ()
                - CALCULATE (
                    MAX ( 'Table 1'[Date Completed] ),
                    ALL ( 'Table 1'[Date Completed] ),
                    VALUES ( 'Table 1'[Team] )
                ),
            DOUBLE
        ) <= 365,
        "Completed",
        "Not Completed"
    )
RETURN
    _count

[AmandaCarriveau]

This is giving "Completed" for everything, even teams where I can see in the raw data they are not complete

[AmandaCarriveau]

Sorry, it is not giving completed for everything, but it is giving completed when it shouldn't. For example, I have an entry for a team that last performed the task 8/10/2022 and it is saying completed

[smpa01 · Accepted solution]

@AmandaCarriveau  can you please try the following instead

Measure = 
VAR _count =
    IF (
        CONVERT (
            TODAY ()
                - CALCULATE (
                    MAX ( 'Table 1'[Date Completed] ),
                    ALL ( 'Table 1'[Date Completed] ),
                    VALUES ( 'Table 1'[Team] )
                ),
            DOUBLE
        ) <= 365,
        "Completed",
        "Not Completed"
    )
RETURN
    _count

[AmandaCarriveau · Accepted solution]

I ended up creating a calculated column to flag if each data is within the past 52 weeks, but thanks for the help!

[v-yohua-msft]

Hi, @AmandaCarriveau 

Glad to hear you solved the problem yourself!  
If you can, please share your solution, which will be beneficial for future users to read this post

Best Regards

Yongkang Hua

. 

[smpa01]

@AmandaCarriveau  you can write a measure like this

Measure =
IF (
    NOT (
        ISBLANK (
            CALCULATE (
                MAX ( 'Table 1'[Team] ),
                FILTER (
                    VALUES ( 'Table 1'[Team] ),
                    'Table 1'[Team]
                        IN SUMMARIZE (
                            FILTER ( ALL ( 'Table 1' ), 'Table 1'[Date Completed] <= TODAY () - 365 ),
                            'Table 1'[Team]
                        )
                )
            )
        )
    ),
    "Completed",
    "Not Completed"
)