Forum Discussion

DmitryAD7's avatar
DmitryAD7
Helper I
2 years ago
Solved

Custom Date Period

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's avatar
    xifeng_L
    2 years ago

    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 )
)

     

     

2 Replies

  • xifeng_L's avatar
    xifeng_L
    Super User
    2 years ago

    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 )
)

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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