Year over year % change without date column

[khanh_dam]

Hello friends,

I am new to Power BI and I was wondering how to get a YoY% for the below data. 

My data table:

year_month	event	country
Y24M04	A	US
Y24M04	A	CA
Y24M04	B	CA
Y24M04	C	CA
Y24M03	D	CA
Y24M03	E	US
Y23M04	F	CA
Y23M04	G	CA
Y23M03	H	US
Y23M03	H	CA
Y23M03	I	US
Y23M03	J	CA

Results I need:

Year_month	event_distinctcount	event_distinctcount_PY	YoY
Y24M04	3	2	50%
Y24M03	2	3	-33%

with a country slicer that can filter the results.

Thank you so much in advance!

Khanh

[Anonymous]

Hi @khanh_dam ,

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!

[khanh_dam]

Hi Yang,

Thank you so much for your response! When I select just Y24 value(s) in the year_month slicer, the results table shows blank for the PY measure. How do we make the PY measure work without having to also select the PY values in the year_month slicer?

Thank you!

[Anonymous]

Hi @khanh_dam ,

Please modify this measure as shown below, all else being equal:

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 (
            ALL ( 'Table' ),
            VALUE ( MID ( 'Table'[year_month], 2, 2 ) ) = _currentYear - 1
                && VALUE ( MID ( 'Table'[year_month], 5, 2 ) ) = _currentMonth
        )
    )

The final visual effect is shown below:

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!

[khanh_dam]

Hi @Anonymous ,

When I modified from ALLSELECTED to ALL then the Country slicer stops applying to the PY measure. Is there a way we can modify it so that this measure will be filtered by all other slicers but not the year_month slicer?

Thank you!

[Anonymous · Accepted solution]

Hi @khanh_dam ,

Please modify the measure syntax as shown below:

event_distinctcount_PY = 
VAR _currentYear =
    VALUE ( MID ( SELECTEDVALUE ( 'Table'[year_month] ), 2, 2 ) )
VAR _currentMonth =
    VALUE ( MID ( SELECTEDVALUE ( 'Table'[year_month] ), 5, 2 ) )
var _Slicer_Country = VALUES('Table'[country])
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[event] ),
        FILTER (
            ALL ( 'Table' ),
            VALUE ( MID ( 'Table'[year_month], 2, 2 ) ) = _currentYear - 1
                && VALUE ( MID ( 'Table'[year_month], 5, 2 ) ) = _currentMonth
                &&'Table'[country] IN _Slicer_Country
        )
    )

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!

[khanh_dam]

@Corey_M , @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:

year_month	year_month_PY
Y24M04	Y23M04
Y24M05	Y23M05
Y24M06	Y23M06
Y24M07	Y23M07
Y24M08	Y23M08
Y24M09	Y23M09

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

Thank you!

[ryan_mayu]

@khanh_dam 

you need to create a date column and a date table

pls see the attachment below

[Corey_M]

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)