Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
martijndulac
Frequent Visitor

Year over year comparison over more than one year

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:

tabletable

 

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

exampleexample

 

1 ACCEPTED SOLUTION

Hi, @martijndulac 

 

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

Like this:6.png

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.

View solution in original post

8 REPLIES 8
MFelix
Super User
Super User

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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?

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?


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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? 

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

 

martijndulac_0-1616016495046.png

 


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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi, @martijndulac 

 

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

Like this:6.png

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.