Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rg72
Frequent Visitor

Last 8 weeks - In order

Hi all.

 

I'm building a graph to show a rolling 8 weeks of tickets resolved by a support team. The data is being pulled directly from service now via odbc.

 

Using help on here provided to others, I've got the graph to show the last 8 weeks of data, however the axis is sorted in numerical order, rather than date order. I've been wracking my brain on how to re-order the axis, but no joy.

 

Screen Shot 2018-01-15 at 11.36.41.png

 

The code used to generate is ...

 

"Week", each Date.ToText(Date.StartOfWeek ([sys_created_on], Day.Monday), "dd/MM") & "-" & Date.ToText(Date.EndOfWeek([sys_created_on],Day.Monday),"dd/MM"))

 

Obviously this is using date to text, hence the ordering issue, but I'm not sure how to proceed.

 

I think I can cut out the 2nd part of the formula (ie the "- xx/xx' part) and just have an effective week commencing date of dd/MM, but it's then how I convert that to be able to get the correct sort order that I'm suck at.

 

Any help would be greatly appreciated.

 

TIA

 

Rob

 

1 ACCEPTED SOLUTION
gooranga1
Power Participant
Power Participant

Hi @rg72,

 

@Rfranca is correct, this is far easier with a date dimension however using your customised field of "dd/MM" will be problematic as you cannot gurantee that that combination is unique for that week over several years which is typically what you get in a date dimension. For example  26/12-01/01 occurs in 2011 and 2016.

 

If you are using just a small amount of data not spanning more than a few years you won't get this problem however. Using the last 90 days of my date dimesnion I created a "dummy" dimension table using your format so that it's order correctly.

 

Create StartDateOfWeek and Custom.2 columns in your original table.

 

Dates.PNG

Copy that table as a new table and group the new table as so.

Dates1.PNG

Then join the new table and original table together on start date of week

Dates2.PNG

Then sort your custom column in the new table.

Dates5.PNG

Then you can use the custom2 field as your axis

Dates6.PNG

And it will ordered correctly.

 

I am not a big fan of date formats like that and I would much prefer to see a date as the start of week in "yyyy-MM-dd" format.

View solution in original post

14 REPLIES 14
vanessafvg
Super User
Super User

@rg72 hi rob what ou will have to do is create an index field and then sort that date range field by the index, i.e if you look on your modelling table, you can sort different fields by other fields using sort by.

 

that should resolve it





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




@vanessafvgYou make it sound so easy 🙂

 

Screen Shot 2018-01-15 at 13.10.22.png

 

Whichever field I choose, I get the same error.

 

If you have a 'Rob is a dummy' step-by-step guide that would be great 🙂

gooranga1
Power Participant
Power Participant

If you change the format of your start date of week it should sort ok.

 

Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")

 

If you format your text differently and just use the start date of the week it will sort ok.

 

Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")

Why don't you just chnage your text formula to return the first date of the week in yyyy-MM-dd format?

 

Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")

That would be in the correct order then.

lol

 

@rg72 ok this will be to do with modelling your data, however the index has to be unique to each part of the range, so you need have only one value for each range value - make sense?

 

ie, #

1 jan

2 feb

1 jan

3 mar

2 feb 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




hi, @rg72@gooranga1@vanessafvg

 

this is easily solved by using a calendar table.
You create a column with the desired format "dd / mm - dd / mm" and indexing correctly through the calendar table itself.

 

Do you already try that?

Is it possible to send a sample of data to help you?

rg72
Frequent Visitor

Thanks all.

 

I must admit to be getting confused by calendar tables etc - as mentioned I'm pulling directly from Service Now, so only have a single table of data in the background.

 

@Rfranca- Thanks for the offer. I have dropped you a PM.

 

@gooranga1- Thanks for the suggestion. I did try this, and may return to it if I can't get any of the others to work.

 

@vanessafvg- I'm much more of a dummy than that 😄 Again with modelling, I only have 1 table, so I guess this is where my problem is.

@rg72 agree calendar table is the way to go, just depends on how much effort you want to put in.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




No problem with effort 🙂

@rg72 so have you got it sorted then?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




I'm still working my way through the above instructions - I'm a PowerBI novice, so it's taking time 🙂

rg72
Frequent Visitor

It looks to be working, however I guess I made a mistake somewhere along the line as the axis is not showing correctly

 

Screen Shot 2018-01-16 at 12.20.42.png

 

I'll have to redo it to see if that resolves it.

 

Many thanks to all for your help 🙂

gooranga1
Power Participant
Power Participant

Hi @rg72,

 

@Rfranca is correct, this is far easier with a date dimension however using your customised field of "dd/MM" will be problematic as you cannot gurantee that that combination is unique for that week over several years which is typically what you get in a date dimension. For example  26/12-01/01 occurs in 2011 and 2016.

 

If you are using just a small amount of data not spanning more than a few years you won't get this problem however. Using the last 90 days of my date dimesnion I created a "dummy" dimension table using your format so that it's order correctly.

 

Create StartDateOfWeek and Custom.2 columns in your original table.

 

Dates.PNG

Copy that table as a new table and group the new table as so.

Dates1.PNG

Then join the new table and original table together on start date of week

Dates2.PNG

Then sort your custom column in the new table.

Dates5.PNG

Then you can use the custom2 field as your axis

Dates6.PNG

And it will ordered correctly.

 

I am not a big fan of date formats like that and I would much prefer to see a date as the start of week in "yyyy-MM-dd" format.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors