Multiple time periods comparison in Power BI / DAX

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.

1 answer

  • answered 2020-10-16 05:32 R_R

    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-

    enter image description here

    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-

    enter image description here