Forum Discussion

Hoggy's avatar
Hoggy
Frequent Visitor
8 years ago
Solved

Date Hierarchy issue on a graph

I am self taught on Power BI and loving it! I developed the perfect report for my needs with data from Jan'18 to August '18. Using a field in my data "Posted Invoice Date".   Once I had the report ...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    This is what you should be doing:

     

    1. Create a calendar table with the following calculated column formulas YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
    2. Create a relationship from the Date column of your base data table to the Date column of your Calendar Table
    3. Using the "Sort by column" feature, sort the Months in the calendar Table by the Order column in the Calendar Table
    4. In your visual, drag Year and Month from the Calendar Table

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
8 years ago

Hi,

 

This is what you should be doing:

 

  1. Create a calendar table with the following calculated column formulas YEAR=YEAR(Calendar[Date]) and Month=FORMAT(Calendar[Date],"mmmm")
  2. Create a relationship from the Date column of your base data table to the Date column of your Calendar Table
  3. Using the "Sort by column" feature, sort the Months in the calendar Table by the Order column in the Calendar Table
  4. In your visual, drag Year and Month from the Calendar Table

 

Hope this helps.

Hoggy's avatar
Hoggy
Frequent Visitor
8 years ago

Ashish, thankyou so much. I knew that I would need to create a Calendar Table for some of my reports but given this one had a date in the correct format I thought I could go without it.

 

Have put a Calendar Table in and it works a treat.

 

Cheers

Hoggy

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    You are welcome.  If my reply helped, please mark it as Answer.