Problem with gaps in data. YoY but without 0 values.

Hi AdamKsiazek

The solution is not trivial because it requires an internal iteration to check the result for the corresponding month in the previous year.

To implement the desired logic, you can follow these steps:

Using Power Query (PQ):
Convert your "Month-Year" column into a proper Date column.

2. Create a date table after closing and applying

3. create a realationship with your table :

4. create year column

5. create the measures:

CurrentYear =

VAR SelectedYear = MAX('Calendar'[Year])

RETURN

SUMX(

    FILTER(

        'Data',

        YEAR('Data'[Date_]) = SelectedYear &&

        'Data'[Value] > 0 &&

        LOOKUPVALUE(

            'Data'[Value],

            'Data'[Date_], DATE(SelectedYear - 1, MONTH('Data'[Date_]), 1)

        ) > 0

    ),

    'Data'[Value]

)

 

Previous Year =

VAR SelectedYear = MAX('Calendar'[Year]) -- השנה הנבחרת מהסלייסר

VAR PrevYear = SelectedYear - 1

RETURN

SUMX(

    FILTER(

        'Data',

        YEAR('Data'[Date_]) = PrevYear &&

        'Data'[Value] > 0 &&

        LOOKUPVALUE(

            'Data'[Value],

            'Data'[Date_], DATE(SelectedYear, MONTH('Data'[Date_]), 1)

        ) > 0

    ),

    'Data'[Value]

)

Result

The results are different from yours because, for some reason, you calculated some months even though it shouldn't have been included based on the required logic, as the value for this month in the one of years was 0.

The pbix and the excel are attached

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