Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Attn Power BI ninjas: Custom Date Range Inquiry

Greetings Power BI ninjas! I am a new Power BI user and need some assistance from the community. I am trying to create a custom date range that will go back 15 years from the date that I manually sel...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous,

     

    You can create an extra table which is unrelated to source data table, to list all available year selections.

    Year selection =
FILTER (
    CALENDAR ( DATE ( 1980, 1, 1 ), DATE ( 2020, 1, 1 ) ),
    MONTH ( [Date] ) = 1
        && DAY ( [Date] ) = 1
)

     

    Later, please add this [date] field into slicer.

     

    Create below measure and add it to visual level filter, set its value to 1.

    flag =
IF (
    MAX ( 'Sheet1 (2)'[Year] ) <= YEAR ( SELECTEDVALUE ( 'Year selection'[Date] ) )
        && MAX ( 'Sheet1 (2)'[Year] )
            > YEAR ( SELECTEDVALUE ( 'Year selection'[Date] ) ) - 15,
    1,
    0
)

     

    Best regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
7 years ago

Hi Anonymous,

 

You can create an extra table which is unrelated to source data table, to list all available year selections.

Year selection =
FILTER (
    CALENDAR ( DATE ( 1980, 1, 1 ), DATE ( 2020, 1, 1 ) ),
    MONTH ( [Date] ) = 1
        && DAY ( [Date] ) = 1
)

 

Later, please add this [date] field into slicer.

 

Create below measure and add it to visual level filter, set its value to 1.

flag =
IF (
    MAX ( 'Sheet1 (2)'[Year] ) <= YEAR ( SELECTEDVALUE ( 'Year selection'[Date] ) )
        && MAX ( 'Sheet1 (2)'[Year] )
            > YEAR ( SELECTEDVALUE ( 'Year selection'[Date] ) ) - 15,
    1,
    0
)

 

Best regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thank you so much!