cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean2
Frequent Visitor

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

Sean2
Frequent Visitor

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

Sean2
Frequent Visitor

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

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors