Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Guys,
I'm quite new to PowerBI and i was wondering if there is any way to Drilldown an Year value by decade.
I have a sample dataset of IMDB top 250 movies and want to drill down by decade, but cant find a way around.
Also i've read somewhere that the max possible value for a date is year. Is that true?
Secondly, i created a column with a "decade value" by dividing the year value by 10, but cant find a way to drill from this decade value to year value.
Anyone has any idea or can suggest any direction that i can follow. Any help is greatly appreciated. : )
To get a decade bucket first extract a Year column from date column. Change the data type of the Year column from date to text.
Then create this custom column.
Decade bucket = var yearlastdigit = RIGHT ( Decade[Year], 1 ) return IF ( yearlastdigit = "0", IF ( YEAR ( Decade[Date] ) < 2000, "19" & INT ( ( YEAR ( Decade[Date] ) - 1900 ) / 10 ) & "0" & " - " & ( CEILING ( YEAR ( Decade[Date] ) / 10, 1 ) * 10 + 10 ) - 1, "20" & INT ( ( YEAR ( Decade[Date] ) - 2000 ) / 10 ) & "0" & " - " & ( CEILING ( YEAR ( Decade[Date] ) / 10, 1 ) * 10 + 10 ) - 1 ), IF ( YEAR ( Decade[Date] ) < 2000, "19" & INT ( ( YEAR ( Decade[Date] ) - 1900 ) / 10 ) & "0" & " - " & ( CEILING ( YEAR ( Decade[Date] ) / 10, 1 ) * 10 ) - 1, "20" & INT ( ( YEAR ( Decade[Date] ) - 2000 ) / 10 ) & "0" & " - " & ( CEILING ( YEAR ( Decade[Date] ) / 10, 1 ) * 10 ) - 1 ) )
You will get the output like this.
Now you can drill down from Decade bucket to year value.
Hi Nippon,
According to your description, you need way to Drilldown an Year value by decade, right?
If that is the case, you can create a column use the DAX below.
Decade = FLOOR(YEAR(Decade[Date])/10,1)*10&"-"&CEILING(YEAR(Decade[Date])/10,1)*10
Regards,
Charlie Liao
Good tip! worked for me 🙂
This message may be a bit late but i struggled to create a Decade column since i have data from 1900 till today. The solutions given here did not meet my requirements (see in the screenshot before me that data in 1960 is given the decade 1960-1960). For anyone who is having the same problem, use this formula:
Decade = If(Year(Calender[Date])<2000,"19" & Int((Year(Calender[Date])-1900)/10) & "0's", "20"& Int((Year(Calender[Date])-2000)/10) & "0's")
You should be able to create a new column like:
Decade = ROUNDDOWN(([Year]-2000)/10,0)*10
Obviously, you'll have to account for 1900's movies, you could do that in an if statement or I'm sure there is a nifty math solution.
Here is a nifty math solution which accounts for both centuries 🙂
Decade = VAR DecadeStart = 'Date'[Year] - MOD ( 'Date'[Year], 10 ) VAR DecadeEnd = DecadeStart + 10 RETURN DecadeStart & " - " & DecadeEnd
How do I show, in a table, the highest rated movie / highest ranked movie in each decade? What about top 2 in each decade?
Also, how do I highlight the decades in which the top 3 movies were released?
User | Count |
---|---|
128 | |
71 | |
70 | |
58 | |
53 |
User | Count |
---|---|
193 | |
96 | |
66 | |
62 | |
52 |