Implement different periods in reports

[gonrodrigues]

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!

[KHorseman · Accepted solution]

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.

[KHorseman · Accepted solution]

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.

[gonrodrigues]

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?

[KHorseman · Accepted solution]

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.

[gonrodrigues]

I'll try to implement those changes.

Thanks again!