Multiple time periods comparison in Power BI / DAX

Question

I have a question regarding a multiple-year comparison report in Power BI and DAX. The first five columns in the below table show the example data, the sixth column shows the two requirements, and the last column shows whether a plan _ID in the first column meets the requirement. I hope to count the number of plan_IDs which meet both requirements for a specific season (e. g. Spring 2018).

enter image description here

As you can see from the last column, Spring 2018 has 3 "yes" while Spring 2019 has 6. Therefore, for Spring 2019, the "Count of Plans for This Year" is 6 while the "Count of Plans for The Last Year" is 3, as shown in the table below. The table is what I want to have.

enter image description here

My question is how to count the plans that meet the two requirements for a specific season/season_number such as Spring 2019/190.

I have been struggling in this situation for a long time. Any ideas or suggestions will be greatly appreciated.

Answer 1

If my understanding (as commented) is correct, just add a Custom Column to your table as below in the Power Query Editor-

Changed Type in line 2 is basically the Previous Step Name. Adjust this accordigly.

= Table.AddColumn(
    #"Changed Type", 
    "counter or not", 
    each 
    let 
        current_year = Date.Year(DateTime.LocalNow()),
        year_difference = current_year - [year] + 1,
        current_date = DateTime.Date(DateTime.LocalNow()),
        date_to_check_with = Date.AddYears(current_date,-(year_difference)),
        meet_req = if [plan_date] <= date_to_check_with then "Yes" else "No"
    in meet_req
)

And here is the final output-

Now your the column with Yes/No value. To count Yes per year, come back to report and create this below Measure-

yearly_yes_count = 
CALCULATE(
    COUNT(your_table_name[counted or not]),
    FILTER(
        ALLEXCEPT(your_table_name,your_table_name[year]),
        your_table_name[counted or not] = "Yes"
    )
)

Here is last year count-

last_year_yes_count = 

VAR current_row_year = MIN(your_table_name[year])

RETURN
CALCULATE(
    COUNT(your_table_name[counted or not]),
    FILTER(
        ALL(your_table_name),
        your_table_name[counted or not] = "Yes"
        && your_table_name[Year] = current_row_year - 1
    )
)

Now add Year and new measure yearly_yes_count to a table visual and the output will be as below-