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 ascending order as 1-jan,2-jan etc. 

Request you to please advise on same

 

I have Date field with hierarchy and i know i can create custom column under modelling ,but will that be suitable option or something else needs to be done

Please advise 

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.

1 Reply

  • 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.