Year over year % change without date column

Hi Anonymous ,

Thanks for the reply from ryan_mayu  / Corey_M .

First, create a measure to calculate the distinct count for the current year.

event_distinctcount = DISTINCTCOUNT('Table'[event])

Then, create a measure to calculate the distinct count for the same period last year.

event_distinctcount_PY =
VAR _currentYear =
    VALUE ( MID ( SELECTEDVALUE ( 'Table'[year_month] ), 2, 2 ) )
VAR _currentMonth =
    VALUE ( MID ( SELECTEDVALUE ( 'Table'[year_month] ), 5, 2 ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[event] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            VALUE ( MID ( 'Table'[year_month], 2, 2 ) ) = _currentYear - 1
                && VALUE ( MID ( 'Table'[year_month], 5, 2 ) ) = _currentMonth
        )
    )

Finally, create a measure to calculate YOY.

YoY = 
DIVIDE(
    [event_distinctcount] - [event_distinctcount_PY],
    [event_distinctcount_PY],
    0
)

The final page result is shown below:

The pbix file is attached.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

this will be much easier with the creation of a separate calendar table (example calendar table) then you will need to either add a column to the calendar table to match your monthyear format or transform your monthyear format to match the calendar table so that they can form a proper relationship.

Once that is done it really is as simple as 

PriorYearEvents = Calculate(
  countrows(EventTable),
  ParallelPeriod(DateTable[Date],-1,year)
)

CurrentYearEvents = Countrows(EventTable)

YOYEvents = Divide((CurrentYearEvents - PriorYearEvents),PriorYearEvents)

Anonymous 

you need to create a date column and a date table

pls see the attachment below

Anonymous , ryan_mayu 

Thank you very much for your quick responses!

In the data I am working with, the year, quarter, and month numbers are not following the normal calendar. For example, 2024/04/01 is Y24M04 but 2023/04/01 might belong to Y23M03. 

So I was hoping for a solution when the year_month slicer is selected for one or more values, Y24M04 and Y24M06 for example, then eventcount measure would calculate for Y24M04 & Y24M06, and eventcount_PY would calculate for Y23M04 & Y23M06, and the YoY is the %difference of those two.

I was thinking of creating a lookup table with 2 columns, year_month, and year_month_PY like:

but I am not sure how to go from there or if that's the right direction.

Thank you!