Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Issues with the Date table

Hi All, 

 

I used the code below to create a Date table for my dashboard. However, when I use the "Month" date to show the full financial year (May2024 to April 2025), the data that appears only shows only two Months (May and June) of data instead of the full financial year of data. 

Date = ADDCOLUMNS(

                  CALENDAR("2019, 01, 01"," 2027, 04, 30"),
                           "Year", YEAR([Date]),
                           "Month Year Num", CONCATENATE(YEAR([Date]), FORMAT([Date],"MM")),
                           "Month Num", Month([Date]),
                           "Month", FORMAT([Date], "MMM"),
                           "Quarter Num",FORMAT([Date], "Q"),
                           "Quarter", CONCATENATE("Q",FORMAT([Date], "Q")),
                           "Day", FORMAT([Date],"D"),
                           "Week", CONCATENATE("Week ",FORMAT([Date], "WW")))
 
 This is how the "Month" date appears in the table. AS IS 

 Also, when you view the "Month" in a table is appears in a disorganised order as showned below. I would expect the date to start from "May (2024) to April (2025). 

Please can you help me to resolve the issue as this is holding me down from progressing with the dashboard creation. 

 

Below is how I would like the table to appear: TO BE

 

In summary, my data table is comparing the budget data against actuals data, hence the budget data is known across the full financial year (May 2024 - April 2025) and hence should apppear in the data table. while only May 2024 and some June 2024 of the Actuals data is known and expected to show on the table. 

 

Thank you!

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

     

    You seem to be combining these two patterns:
    Custom time-related calculations – DAX Patterns

    Budget – DAX Patterns

     

    // However, when I use the "Month" date to show the full financial year (May2024 to April 2025), the data that appears only shows only two Months (May and June) of data instead of the full financial year of data. 

    This may be related to relationships and filters in the model, check the filters to make sure that the other months are not empty under the current filter. Then review the relationship to make sure that the date table is correctly related to the data table. Incorrect or missing relationships can lead to unexpected results for visualizations.

     

    //  Also, when you view the "Month" in a table is appears in a disorganised order as showned below. I would expect the date to start from "May (2024) to April (2025). 

    For a field of type text, we can select it and sort it by another column.

    Sort one column by another column in Power BI - Power BI | Microsoft Learn

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous , 

       

      Thank you for providing feedback. When I format the transaction date, it displays normally in the GL table as 15/6/2024. However, when I view the same date in the visualization table of a dashboard, it displays in the format shown in the snippet below. Could you please tell me how to reformat the date so that it appears in the dashboard as 15/06/2024?

       

      Kind regards,

      Jonathan.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi Anonymous ,

        You seem to have Auto Date/Time turned on, try unchecking this option.

        You can also right click on the field here and select Date.

        It can also be further formatted here.

        Best Regards,
        Gao
        Community Support Team

         

        If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
        If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

        How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group