Change date on X-axis to be monthly/daily/yearly instead of showing every run individually

[hima2002]

I have this chart that shows if a run has succeeded or failed, the graph currently shows every run individually on the X-axis. I would like to have the option to change it to a monthly view or yearly or daily and to have it show the percentage of failed to succeeded in that time frame instead of showing them for each job. Can I get some help with this?

[hnguy71 · Accepted solution]

@hima2002 ,

You would need a minimum of three columns in your date table. Similar to this:

Then click on Modeling > New Parameter > Fields

Then add/drag your fields from your date table to the left box.

Then finally add the new "Fields" value into your X-Axis of your visual. You should be able to switch it like so

 

[hnguy71 · Accepted solution]

@hima2002 ,

You would need a minimum of three columns in your date table. Similar to this:

Then click on Modeling > New Parameter > Fields

Then add/drag your fields from your date table to the left box.

Then finally add the new "Fields" value into your X-Axis of your visual. You should be able to switch it like so

 

[hima2002]

Thanks for the quick response !

However my dates are currently in this format. How can I change them so that I am able to choose the desired granularity.

[Greg_Deckler]

@hima2002 You can create the columns as I posted or you can turn on Auto-Time Intelligence (I wouldn't recommend the latter)

[hima2002]

Yes but i have a very large amount of data, changing it to the proposed format would take a long time to do indivually. Is there a way to change automatically?

[Greg_Deckler]

@hima2002 I don't understand why it would take a long time. Creating DAX columns generally takes mere seconds to calculate. If you turn on auto-time intelligence that would automatically generate a hierarchy for any Date or Date/time column. I guess you could consider that "automatic". The downside is that it can really bloat your data model. I've shrunk data models by up to 40% by just turning off auto-time intelligence.

[hima2002]

Im new to PowerBi so i dont know exactly how to do that.

[Greg_Deckler · Accepted solution]

@hima2002 OK, so if you want to turn on auto time intelligence, in the Desktop you go to File, Options and settings, Options and then under CURRENT FILE choose Data Load and check the box for Auto date/time:

If you want to create calculated columns, in your Fields pane, right click the table and choose New column and paste in the DAX code I provided. Note, the code is for 4 separate columns, Year, Month, MonthSort and Day. Then, right-click the table and choose New measure and paste in the DAX measure code provided.

[Greg_Deckler]

@hima2002 So, create columns for Month, Year and Day:

Year = YEAR([Date])

Month = FORMAT([Date],"mmmm")

MonthSort = MONTH([Date])

Day = DATE(YEAR([Date]),MONTH([Date]),DAY([Date]))

Then a measure something like:

Measure =
  VAR __Total = COUNTROWS('Table')
  VAR __Failed = COUNTROWS(FILTER('Table',[Status] = "Failed"))
RETURN
  DIVIDE(__Failed, __Total, 0)