Power Bi Sorted Date Table

Question

I have an identified date within a fact table and I need to create a new table that's formatted "MMM-YY" (JAN-01) and sorted in month order.

I entered the data manually from a Excel Spreadsheet with dates ranging from Jan-15 to Dec-30 and sorted by an id num but ideally I want it to update monthly based on a identified date column within the fact table in BI. So for now id want dates upto Sep-19 which would update when the identified dates are refreshed within the fact table.

For now I've used the below to format the column which was inserted manually however id like to change the monthref.date to be based off of an identified date within my fact table.

ID Date = FORMAT('Month Ref'[Date],"MMM-YY")

Id want my date within the fact table to be lookedup/refrenced in another table formatted "mmm-yy" and to be in order of calendar month.

I can use CALENDAR(MIN(FactEfficiencies[identified_date].[Date]) AND MAX(FactEfficiencies[identified_date].[Date])) to get every date then format but it will bring back the month multiple times when I just need one.

I'd suggest you're better off not storing your dates as strings. If you want dates to appear a certain way to the end user, it's best to use the format dropdown in modelling tab on the ribbon to determine the appearance of (in this case) a month column in your date dim table. — Rory

Jelle Hoekstra Jelle Hoekstra · Accepted Answer · 2019-09-16T13:27:59

You are already on the good direction with the calendar table. One thing that could help you achieve your result could be including a SUMMARIZE or GROUPBY function. Have a look at this example.

Calendar = 
    var _fullCalendar = 
        ADDCOLUMNS (
            CALENDAR ( MIN ( 'Table'[Date] ) ; MAX ( 'Table'[Date] ) ) ;
            "MonthYear" ; FORMAT ( [Date] ; "MMM-YY" )
        )
RETURN
    SUMMARIZE ( _fullCalendar ; [MonthYear] )

Power Bi Sorted Date Table

1 Answers