Confusion about date table and date hierarchies for setting up column chart visual

[pbi_learner_7]

Hello - I am a fairly beginner user and have been trying to update a report to use a date table instead of the existing date column in the fact table. The data are fairly simple and there is only one fact table but the fiscal years and quarters do not follow the calendar year, so I wanted to add these as columns in a date table and be able to summarize by those periods in visuals.

In the existing version of the report, I have a colum chart that looks like this. I want to basically recreate this with months from the date table and be able to drill down to fiscal quarters and years.

This version uses a date hierarchy built from the date column in the main table with date and month.

I made a date table and have marked it as such and chosen the date column as containing dates. No problems. And made a connection to the fact table. It seems to be working fine overall as far as the connection. But I cannot replicate the above visual (atlthough it works ok if the x-axis is all the dates). If I just add months from the date table, they are not sequential. I think I understand why this doesn't work, but I also then tried to create a date hierarchy within the date table, and adding date and month and quarters etc. from that to the x-axis does not work - the months are alphabetized.

I feel like I must have a basic gap in understanding here, but I have read and watched a number of tutorials on setting up date tables, as well as forum posts, and I have not found a step by step guide to actually connecting the date table to this type of visual. Any help would be appreciated. Thanks!

[MasonMA · Accepted solution]

Hi:

First, i'd suggest you create your Date table in Power Query with Melissa de Korte's M code. 
Extended Date table M function | Creates an ISO-8601 type calendar · GitHub.

while creating your Date table you can customize your column selections, starting date of fiscal year etc. 

After you build your relationship between your master Date table and Fact table. To avoid alphabetical sorting of months (January, February…), you will need to sort Month by 'Month Number' (this has to be one of the columns you selected from Mellisa's M funciton). Select the 'Month 'column in the date table, then go to Column tools, 'Sort by Column' choose 'Month Number'. You can repeat this logic for 'Fiscal Month' and 'Fiscal Quarter'. 

Once done, You can build your date hierarchy as you did in your previous date table and apply them on your visuals. 

If this is still not working, for the workaround, In one of my past reports i added Month Number in the Tooltips and from there i would be able to Sort by Month Number on the visual. this also enables Visuals sorted by Month etc.. 

[ajaybabuinturi · Accepted solution]

Hi @pbi_learner_7,

I believe your on the right track.
Step1: Make sure you have a proper date table and having minimum below columns

• Date (a column with one row per day)
• Month Name (e.g., January, February)
• Month Number (1 = Jan, 2 = Feb, etc.)
• Year
• Fiscal Month Name (e.g., Jan-25)
• Fiscal Month Number (25-1 = Jan, 25-2 = Feb,....26-1 = Jan, 26-2 = Feb, etc.)
• Fiscal Quarter (e.g., 25Q1,.....25Q4 )

Step2: Already you marked as a Date Table using the Date column.

Step3: Make sure the relationship b/w Fact and Date table is Many to One and it should be Single direction.

Step4: I'm asumming you missed this step

Power BI sort the Month Name by alphabetic oreeder by default. That you need fix the issue with below steps.

• Go to Data View
• Select Month Name column
• In the ribbon, choose "Sort by Column" → choose Month Number

Step4: Repeat the same for Fiscal Month Name.
Sample DAX for DateTable

DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN(Fact[Date Column]), MAX(Fact[Date Column]),),
    "Month Name", FORMAT([Date], "MMMM"), 
    //For short month name use only three M (MMM)
    "Month Number", MONTH([Date]),
   "Year", YEAR([Date]),
   “Fiscal Month Name”, FORMAT(YEAR([Date]), “YY”) & “-“ & FORMAT([Date], "MMM"),
   “Fiscal Month Number”, FORMAT(YEAR([Date]),”YY”) & “-“ & MONTH([Date])
    "Fiscal Quarter", 
        SWITCH(TRUE(),
            MONTH([Date]) >= 1 && MONTH([Date]) <= 3, "Q1",
            MONTH([Date]) >= 4 && MONTH([Date]) <= 6, "Q2",
            MONTH([Date]) >= 7 && MONTH([Date]) <= 8, "Q3",
            "Q4"
        ),
        )
)

Using above steps you will get the expected results. 

If not solved your problem, please sahre some sample data and expected results snippets.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[v-ssriganesh]

Hi @pbi_learner_7,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @pbi_learner_7,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @pbi_learner_7,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @pankajnamekar25, @ajaybabuinturi & @MasonMA for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution? If so, please mark it as the solution. This will help other community members solve similar problems faster.

Thank you.

[pankajnamekar25 · Accepted solution]

Hello @pbi_learner_7 

Here you can explore in details and can download M Code to create Fiscal date table

Extended Date Table (Power Query M function) - Power Query / M Code Showcase - Enterprise DNA Forum

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[ajaybabuinturi · Accepted solution]

Hi @pbi_learner_7,

I believe your on the right track.
Step1: Make sure you have a proper date table and having minimum below columns

• Date (a column with one row per day)
• Month Name (e.g., January, February)
• Month Number (1 = Jan, 2 = Feb, etc.)
• Year
• Fiscal Month Name (e.g., Jan-25)
• Fiscal Month Number (25-1 = Jan, 25-2 = Feb,....26-1 = Jan, 26-2 = Feb, etc.)
• Fiscal Quarter (e.g., 25Q1,.....25Q4 )

Step2: Already you marked as a Date Table using the Date column.

Step3: Make sure the relationship b/w Fact and Date table is Many to One and it should be Single direction.

Step4: I'm asumming you missed this step

Power BI sort the Month Name by alphabetic oreeder by default. That you need fix the issue with below steps.

• Go to Data View
• Select Month Name column
• In the ribbon, choose "Sort by Column" → choose Month Number

Step4: Repeat the same for Fiscal Month Name.
Sample DAX for DateTable

DateTable = 
ADDCOLUMNS (
    CALENDAR (MIN(Fact[Date Column]), MAX(Fact[Date Column]),),
    "Month Name", FORMAT([Date], "MMMM"), 
    //For short month name use only three M (MMM)
    "Month Number", MONTH([Date]),
   "Year", YEAR([Date]),
   “Fiscal Month Name”, FORMAT(YEAR([Date]), “YY”) & “-“ & FORMAT([Date], "MMM"),
   “Fiscal Month Number”, FORMAT(YEAR([Date]),”YY”) & “-“ & MONTH([Date])
    "Fiscal Quarter", 
        SWITCH(TRUE(),
            MONTH([Date]) >= 1 && MONTH([Date]) <= 3, "Q1",
            MONTH([Date]) >= 4 && MONTH([Date]) <= 6, "Q2",
            MONTH([Date]) >= 7 && MONTH([Date]) <= 8, "Q3",
            "Q4"
        ),
        )
)

Using above steps you will get the expected results. 

If not solved your problem, please sahre some sample data and expected results snippets.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[MasonMA · Accepted solution]

Hi:

First, i'd suggest you create your Date table in Power Query with Melissa de Korte's M code. 
Extended Date table M function | Creates an ISO-8601 type calendar · GitHub.

while creating your Date table you can customize your column selections, starting date of fiscal year etc. 

After you build your relationship between your master Date table and Fact table. To avoid alphabetical sorting of months (January, February…), you will need to sort Month by 'Month Number' (this has to be one of the columns you selected from Mellisa's M funciton). Select the 'Month 'column in the date table, then go to Column tools, 'Sort by Column' choose 'Month Number'. You can repeat this logic for 'Fiscal Month' and 'Fiscal Quarter'. 

Once done, You can build your date hierarchy as you did in your previous date table and apply them on your visuals. 

If this is still not working, for the workaround, In one of my past reports i added Month Number in the Tooltips and from there i would be able to Sort by Month Number on the visual. this also enables Visuals sorted by Month etc..