Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

YTD Dates & Sorting

Folks, Trying my hands on typial developement of Power BI first time from other tools now 1. Want to display YTD dates (1-jan till today) as one of the axis 2. want to sort those values in ascendi...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous 

    I suggest you to create a calendar table by dax and then relate this date table with your fact table by date columns.

    Date Table:

    Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName",FORMAT([Date],"MMMM"),
    "MonthShortName",FORMAT([Date],"MMM"),
    "Day", DAY ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Mon-Day", FORMAT ( [Date], "dd-mmm" )
)

    Result is as below.

    Then you can create your visuals with Date hierachy.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi Anonymous 

I suggest you to create a calendar table by dax and then relate this date table with your fact table by date columns.

Date Table:

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "MonthName",FORMAT([Date],"MMMM"),
    "MonthShortName",FORMAT([Date],"MMM"),
    "Day", DAY ( [Date] ),
    "YearMonth",
        YEAR ( [Date] ) * 100
            + MONTH ( [Date] ),
    "Mon-Day", FORMAT ( [Date], "dd-mmm" )
)

Result is as below.

Then you can create your visuals with Date hierachy.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.