Forum Discussion

ElliotP's avatar
ElliotP
Icon for Post Prodigy rankPost Prodigy
10 years ago
Solved

Starting Graph Dates from beginning of Data

Hi guys,

 

Photo: https://gyazo.com/bc163b83a319cae5de6251c2a06bd1f9

 

I've tried hierachies, sorting, constructing new columns, etc, etc. I'm a a bit of loss.

 

I'd like to start my graph from July 2015 so it goes until June 2016; As opposed to now where it is Jan16-June16 THEN July15-December15.

 

I've tried sorting the date column and it doesn't seem to pay any attention to that in the graph even if I use the original date column (set as a date), a calculated column with format. I run into the issue of where you can't create a calculated column of the month from the original date column and then sort accordingly as you recieve the error "can't sort many to one" or something similar.

 

Any help would be greatly appreciated!

Need Help
  • Anonymous's avatar
    Anonymous
    10 years ago

    In order to sort by another column you need a number column that has a single matching value for each month. This thread ran into the same issue you're having, so the same solution should work:

     

    Month Val = INT(CONCATENATE(YEAR(DateTable[Date]), CONCATENATE(IF(MONTH(DateTable[Date]) < 10, "0", ""), MONTH(DateTable[Date]))))

4 Replies

  • kcantor's avatar
    kcantor
    Icon for Community Champion rankCommunity Champion
    10 years ago

    Do you have a separate date table? I use the year-month method and can have as many years as I have data on the same graph but I have to build my year-month column into the date table and relate the fact table to it.

    It appears that your date is coming from your fact table.

    Here is alink on date tables.

    http://www.powerpivotpro.com/2011/11/the-ultimate-date-table/

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    In order to sort by another column you need a number column that has a single matching value for each month. This thread ran into the same issue you're having, so the same solution should work:

     

    Month Val = INT(CONCATENATE(YEAR(DateTable[Date]), CONCATENATE(IF(MONTH(DateTable[Date]) < 10, "0", ""), MONTH(DateTable[Date]))))

    • ElliotP's avatar
      ElliotP
      Icon for Post Prodigy rankPost Prodigy
      10 years ago

      Morning,

       

      Thank you so much Anonymous; that's exactly what I needed.

       

      I did what you suggested, created a new column with your code. Then I created two new columns for Month and Day using:

      CalcDay = FORMAT('All Web Site Data'[Date - Copy], "dd-mmm-YYyy") */Day
      CalcMonth2 = FORMAT('All Web Site Data'[Date - Copy], "yy-mmm") */Month

      And then I sorted those two newly created columns by your column and that works perfectly!

       

      I'm going to read the link about dates as I feel that's pretty important. My dates are in the fact table and there is no way for me to get them as a seperate table unless I were to either do manual data entry OR I think what the link to data tables suggests.

       

      My data is from Google Analytics btw, it requires you to take a measure as well as something else for it to even load the data; In the future maybe I'll just export google analytics data to an sql server and do it like that.

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        You can still use a separate date dimension table with google analytics and write your own measures. I think you can even use some of their out of the box measures and they'll play nice with that setup. One of the great strengths of Power BI is its time intelligence functionality, and that relies on having a date table, which is where all my recommended columns would go. Then you just create a relationship between that date table's main date column and the date column in your page visits table or whatever.