cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Drill by decade

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. : )

7 REPLIES 7
ankitpatira Super Contributor
Super Contributor

Re: Drill by decade

If you've created decade column then it is very easy. In the axis option of your visual drop decade field followed by year field and that will allow you to drill down from decade to year.
Super User
Super User

Re: Drill by decade

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.

decade.png

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Moderator v-caliao-msft
Moderator

Re: Drill by decade

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

Capture.PNG

 

Regards,

Charlie Liao

Tamerlane Frequent Visitor
Frequent Visitor

Re: Drill by decade

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")

Chiranjit Frequent Visitor
Frequent Visitor

Re: Drill by decade

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.

 

8.JPG

 

Now you can drill down from Decade bucket to year value.

dataloreous Frequent Visitor
Frequent Visitor

Re: Drill by decade

Here is a nifty math solution which accounts for both centuries Smiley Happy 

 

Decade =
VAR DecadeStart =
    'Date'[Year] - MOD ( 'Date'[Year], 10 )
VAR DecadeEnd = DecadeStart + 10
RETURN
    DecadeStart & " - " & DecadeEnd
adarshjeyes New Member
New Member

Re: Drill by decade

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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 221 members 2,514 guests
Please welcome our newest community members: