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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
whothefoo
Frequent Visitor

Single year from date range

Hi, my suspicions are that this may be impossible but...

I am kind of reinventing the wheel in an attempt to increase my BI knowledge, so I am making a football based BI as it's something I have a strong interest in... 

 

Problem is football runs from seasons, but the data I can get from available sources often works from season ranges, for example a player could be at a club from 2000-2007, but I want to create the squads based on this sort of info so I can ask 'who was in the 2000-2001 team?' 

 

So the question: Is there a way of separating out the year groups into duplicate rows?

 

Edgar Chadwick
England
FW1888–1898

 

becomes 

Edgar Chadwick  FW 1888-1889

Edgar Chadwick  FW 1889-1890

Edgar chadiwck  FW 1890-1891

and so on...

 

Reason being I am going to get manager info and build the squads so its possible to choose a season - 1889-1890 for example and it will show Edgar Chadwick in that squad.

 

Hope that makes sense and any help/alternatives would be greately appreciated.

 

Many thanks

 

Wayne

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

2 REPLIES 2
parry2k
Super User
Super User

@whothefoo seems interesting stuff to do. In this case you want to create fiscal calendar which is basically a season based and then have a column showing the year 2000-2001 , 2002-2003 etc. All the magic is going to come from fiscal calendar dimension. Hope it make sense. You can google on fiscal calendar and I'm sure you will get many blogs on how to use it.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Yes 🙂 took a bit of playing around with things I didn't understand but it's sorted now.

Thank you

 

If anyone is interested it's a mixture of using left and right commands to make two columns with the start and end year from the 2010-2015 style range.

 

Subtract the start column from the end to give a count of how many duplicate rows you need then follow the below:

https://www.youtube.com/watch?v=cXb308NPU2M

 

* I did have to add the final index column into the formula when expanding the rows but it's given me a duplicate row for each with a value of 0-however many dupliates there are.

 

Now I can just add that value to the start date and add one to create the end date and concat back to get a start to end range - for 2010-2015 - index 3 - add index (in the case 3) to start (2010) add one more for the end date and concat = 2013-2014.

For the next dipicate row the index is 4 so it will make 2014-2015.

 

phew lol

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.