Forum Discussion

NipponSahore's avatar
NipponSahore
Resolver II
10 years ago

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, b...
Need Help
Tutorial Requests
v-caliao-msft's avatar
v-caliao-msft
Microsoft Employee
10 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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