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
Sean2
Helper I
Helper I

Comparing years from separate sources

Hello knowledgeable ones.

I’m trying to create a data model that can be used to compare multiple years on a line chart. With a line for hours of each activity, that can be filtered/sliced to show all years or just one at a time. And another line chart for hours of activity by day of week for the same.

Thanks for any ideas

 

Here's a sample of the data I'm working with:

https://drive.google.com/drive/folders/1JzcPmlL7egqLzsbJz72tMW0mRpdh0nD-

1 ACCEPTED SOLUTION
aj1973
Community Champion
Community Champion

@Sean2 

Go to Date table, select month name column then sort by "Month Sort"

aj1973_0-1621529518122.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

5 REPLIES 5
aj1973
Community Champion
Community Champion

Hi @Sean2 

I did some cleaning and transforming on your model. and I got this

aj1973_0-1620509810387.png

 

I am not sure where do you get the Tables (2018,2019....) from but you need delete un necessary columns from them and append them. Then add column for number of

Hours = DATEDIFF('Append Tables'[In],'Append Tables'[Out],HOUR)

and another column for the

Year Activity = YEAR('Append Tables'[In])  (by th way I chose dates IN, you pick what you want)

 

One more thing, in your table 2021, you have dates IN and OUT like here 

aj1973_1-1620510379355.png

that don't make sense and need attention.

 

Last thing: I uploaded the Pbix file that I corrected to your drive.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Thanks Amine! That helps a great deal and I think I'm on the right track to what we need. You are right there are some problems with the data integrity in this sample and I will need to correct them.

 

The visual I'm looking to get would be like the area chart at the bottom, but show January through December and if multiple years were selected it would compare the hours of each year.

Do you have any ideas how that could be acomplished?

 

Thanks!

aj1973
Community Champion
Community Champion

Hi @Sean2 

Not sure I understood, is this what you want to see

For years

aj1973_0-1620916744951.png

 

Months

aj1973_1-1620916876676.png

aj1973_2-1620916995985.png

 

 

 

 

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Amine,

Yeah esentially like either one of those last two screen shots. Showing each month in chronological order. I've got close to what I'm looking for, but the months always show up in alphabetical order instead of chronological order.

 

aj1973
Community Champion
Community Champion

@Sean2 

Go to Date table, select month name column then sort by "Month Sort"

aj1973_0-1621529518122.png

 

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.