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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
gonrodrigues
Helper III
Helper III

Implement different periods in reports

This NASA/ESA report is amazing btw:

 

https://app.powerbi.com/view?r=eyJrIjoiYTMwNDZjNzAtMTc5ZS00YmIyLWJiNDAtYzRjZmJiMDQ3YmI0IiwidCI6IjU3N...

 

 

How can I implement the 'period' thing, to visualize the same graphs but in different years?

 

Thanks!

2 ACCEPTED SOLUTIONS
KHorseman
Community Champion
Community Champion

The source data clearly has dates (or at least years) associated with each record (each mission?). Having years at hand, it would be easy to create another column that lumps groups of dates/years together. Perhaps something like...

 

Period = SWITCH(TRUE(),

Missions[Year] >= 1958 && Missions[Year] <= 1969, "1959-1969",

Missions[Year] >= 1970 && Missions[Year] <= 1979, "1970-1979",

Missions[Year] >= 1980 && Missions[Year] <= 1989, "1980-1989",

Missions[Year] >= 1990 && Missions[Year] <= 1999, "1990-1999",

Missions[Year] >= 2000 && Missions[Year] <= 2015, "200-2015",

BLANK()

)

 

Then make a horizontal slicer using that Missions[Period] column as its value and stick it at the bottom of the page.

 

 

Edit: on further examination it's definitely a year field, not a date field. The years on some of the column charts and such were accidentally left formatted as regular whole numbers (e.g. 1,990 instead of 1990). If it had been a date field, you could modify my formula by wrapping the dates in a YEAR formula. For instance, YEAR(Missions[Date]) >= 2000 && Missions[Year] <= 2015, "200-2015". Which one of these two is right for your own dataset? I have no idea obviously.





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

Proud to be a Super User!




View solution in original post

I don't know exactly what's going on in your particular case but if it's the same type and structure of data separated into different excel files by year (In other words, these Excel files all have the same set of columns, just with different data by year, right?), I would just combine them all into one table by appending queries.

 

Assuming your Excel files don't already have a year column, start out by either 1) adding a year column to each Excel file or 2) querying each Excel file and adding a year column in the query. If you are going to alter these source files frequently in the future, option 2 may be the easier. If they're static files with old data that you don't expect to ever change or add to, option 1 is probably best. But technically speaking either will work in either case.

 

The result should be one table in Power BI that has the data from all of the Excel files plus a year column. Really you should do this anyway, even if you weren't trying to do the year slicer thing. If all those files represent the same set of data for different time periods, that should be a single table. Otherwise you would have to write the same measures over and over with slightly different names for each table, and they would all be unrelated to each other.





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

Proud to be a Super User!




View solution in original post

4 REPLIES 4
KHorseman
Community Champion
Community Champion

The source data clearly has dates (or at least years) associated with each record (each mission?). Having years at hand, it would be easy to create another column that lumps groups of dates/years together. Perhaps something like...

 

Period = SWITCH(TRUE(),

Missions[Year] >= 1958 && Missions[Year] <= 1969, "1959-1969",

Missions[Year] >= 1970 && Missions[Year] <= 1979, "1970-1979",

Missions[Year] >= 1980 && Missions[Year] <= 1989, "1980-1989",

Missions[Year] >= 1990 && Missions[Year] <= 1999, "1990-1999",

Missions[Year] >= 2000 && Missions[Year] <= 2015, "200-2015",

BLANK()

)

 

Then make a horizontal slicer using that Missions[Period] column as its value and stick it at the bottom of the page.

 

 

Edit: on further examination it's definitely a year field, not a date field. The years on some of the column charts and such were accidentally left formatted as regular whole numbers (e.g. 1,990 instead of 1990). If it had been a date field, you could modify my formula by wrapping the dates in a YEAR formula. For instance, YEAR(Missions[Date]) >= 2000 && Missions[Year] <= 2015, "200-2015". Which one of these two is right for your own dataset? I have no idea obviously.





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

Proud to be a Super User!




That was helpful, thanks a lot! @KHorseman

 

I'm completely new with PBI and noob with DB's. I would need a slicer with different years, but each year would correspond to a different excel doc... is that possible?

I don't know exactly what's going on in your particular case but if it's the same type and structure of data separated into different excel files by year (In other words, these Excel files all have the same set of columns, just with different data by year, right?), I would just combine them all into one table by appending queries.

 

Assuming your Excel files don't already have a year column, start out by either 1) adding a year column to each Excel file or 2) querying each Excel file and adding a year column in the query. If you are going to alter these source files frequently in the future, option 2 may be the easier. If they're static files with old data that you don't expect to ever change or add to, option 1 is probably best. But technically speaking either will work in either case.

 

The result should be one table in Power BI that has the data from all of the Excel files plus a year column. Really you should do this anyway, even if you weren't trying to do the year slicer thing. If all those files represent the same set of data for different time periods, that should be a single table. Otherwise you would have to write the same measures over and over with slightly different names for each table, and they would all be unrelated to each other.





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

Proud to be a Super User!




I'll try to implement those changes.

 

Thanks again! Smiley Happy

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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