Year over year comparison over more than one year

[martijndulac]

Hi,

I'm quite a newby with Power Bi, running into some challenges..

We have some courses every year, and I want to compare te running total of entries year over year.

Our fiscal year starts on the 1st of August, but course entries start earlier. So someone might register to follow a course in our fiscal (school) year 2020-2021 in the spring of 2020. But we also have short courses, so someone could also register for a training course taking place in may 2021, and register in April 2021.

So in my visual, I want about 18 months, starting January 1st, and continue until July the next year, and compare all registrations for courses taking place in one fiscal year.

I did some tests with recalculating subscription date to the same year and using a custom date table, but I did not really get the result I hoped for yet..

What would be the best approach to achieve this?

My data to start with is a table of registrations, something like:

table

And here is an example of what I am trying to achieve 

example

[MFelix]

Hi @martijndulac ,

I assume that the month you show on the x-axis is the month of the subscription, so in this case you only need to add the date of subscription on the axis and the fiscal year on the legend that will show all the values within that time range, then you can change by use of a slicer or the filter pane the period,.

If this does not help to achieve the final eresult can you please explain a little bit better what is the expected result?

[martijndulac]

Hi @MFelix,

Thanks, but I think it's a little more complicated. The subscription period is longer than one year. So in this month (march 2021) I have subscriptions for the current fiscal year. And also subscriptions for next year. And they have a different place on the x-axis. So it's not possible to leave out the year tot get a correct result.

So what I could do:

1. create a 1 1/2 year date table starting 1-1-2020 ending 31-07-2021

2. add a measure or column to my subscription tabel recalculating all data so they fit into that date table

3. add measures for every year to the date table for running totals..

Am I making any sense?

[MFelix]

Hi @martijndulac ,

Do you have for every subscription the Fiscal year it correspond? Meaning if we have a subscritpion fdor 2020-2021 aznd another for  2021-2022 you have two lines with the same date  (march) but two values for the fyscal year correct?

In this case there is no need of additional columns or information since the fiscal year makes your filtering has you need.

However if the fiscal year is also the same you need to go to another level where you identify those values.

wich of the options is correct?

[martijndulac]

Hi Miguel,

Yes, I have a column with the fiscal year. Maybe it's really simple, and I am just complicating things:)

So it's easy to filter by fiscal year, and make the Visual with one line of that year. But how do I get the different years in one visual like in the example? 

[MFelix]

You need to add the fiscal year on the legend of the chart.

[martijndulac]

But that doesn't really give me the expected result. Because the dates are a year apart I don't have a year over year comparison like in the first example.

If I follow you suggestions I end up with something like the chart below.

My pbix: link

[MFelix]

Hi @martijndulac ,

For what I can see you want the cumulative values can you tell me if you need the count of the ID or the sum of the values? How is the cumulative calculated?

The measure you used since it's making the use of VALUES of fiscal year has a filter is giving this strange visualization.

[v-janeyg-msft · Accepted solution]

Hi, @martijndulac 

You need to change the data type of the date column to 'date' instead of date/time.

Like this:

Best Regards

Janey Guo

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