Custom Date Period

[DmitryAD7]

Hello community,

Please help, I need to create a table with custom periods:

Last 15 Months

Previous 15 Months

Years

I create all I needed in the calendar table:

Calendar = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2021, 1, 1 ), TODAY () ),
    "Year", FORMAT ( [Date], "yyyy" ),
    "Quarter", FORMAT ( [Date], "Q" ),
    "Month", FORMAT ( [Date], "mmm" ),
    "#Month", FORMAT ( [Date], "mm" ),
    "MonthYear", FORMAT ( [Date], "mmmm yyyy", "en-US" ),
    "Weekday", FORMAT ( [Date], "ddd" ),
    "#Weekday", FORMAT ( [Date], "w", "en-US" ) * 1000,
    "Day", FORMAT ( [Date], "dd" ),
    "Last 15 Months",
        IF (
            ( YEAR ( TODAY () ) - YEAR ( [Date] ) ) * 12
                + MONTH ( TODAY () )
                - MONTH ( [Date] ) + 1 <= 15,
            "Last 15 Months"
        ),
    "Previous 15 Months",
        IF (
            AND (
                ( YEAR ( TODAY () ) - YEAR ( [Date] ) ) * 12
                    + MONTH ( TODAY () )
                    - MONTH ( [Date] ) + 1 > 15,
                ( YEAR ( TODAY () ) - YEAR ( [Date] ) ) * 12
                    + MONTH ( TODAY () )
                    - MONTH ( [Date] ) + 1 <= 30
            ),
            "Previous 15 Months"
        )
)

But I can't get correct result in a custom table:

Custom Periods = 
UNION (
    ADDCOLUMNS (
        'Calendar',
        "Custom Period", 'Calendar'[Last 15 Months],
        "Sort", 1
    ),
    ADDCOLUMNS (
        'Calendar',
        "Custom Period", 'Calendar'[Previous 15 Months],
        "Sort", 2
    ),
    ADDCOLUMNS ( 'Calendar', "Custom Period", 'Calendar'[Year], "Sort", 3 )
)

I have blanks and unnecessary columns in a table . 

 
pbix

[xifeng_L · Accepted solution]

Hi @DmitryAD7 

Is this the result you want?

If yes, you can use the below code:

Custom Periods = 
UNION (
    ADDCOLUMNS (
        FILTER('Calendar','Calendar'[Last 15 Months]<>BLANK()),
        "Custom Period", 'Calendar'[Last 15 Months],
        "Sort", 1
    ),
    ADDCOLUMNS (
        FILTER('Calendar','Calendar'[Previous 15 Months]<>BLANK()),
        "Custom Period", 'Calendar'[Previous 15 Months],
        "Sort", 2
    ),
    ADDCOLUMNS ( 'Calendar', "Custom Period", 'Calendar'[Year], "Sort", 3 )
)

[v-xuxinyi-msft]

Hi @DmitryAD7 

I don't quite understand what your needs are, could you please show the result you want in excel form?  That would be very helpful. Thank you for your time and efforts in advance.

Best Regards,
Yulia Xu