Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Creating a DAX Expression for Rolling Dates Based on Selected Month in Power BI in (direct query)

I’m working on a Power BI report where I have a date slicer based on a ‘Date’ column from a table. When I select a date from the slicer, I want the report to display all dates that fall on the same day and month across different years.

For example, if I select ‘5-31-2011’ from the slicer, the report should display ‘5-31-2011’, ‘5-31-2012’, ‘5-31-2013’, and so on. Essentially, I need rolling dates based on the selected day from the selected month.

I understand that I need to write a DAX expression to achieve this, but I’m not sure how to go about it. I would appreciate any guidance or sample DAX expressions that could help me achieve this functionality. Please note that I would like to implement this without using my specific table and column names.

Thanks in advance for your help!

3 Replies

  • aduguid's avatar
    aduguid
    Icon for Memorable Member rankMemorable Member
    2 years ago

    Create two new columns in your date table to extract the day and month

     

    Day = DAY('DateTable'[Date])
Month = MONTH('DateTable'[Date])

     

    Create a measure that filters the data based on the selected date’s day and month

     

    IsSameDayAndMonth = 
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
VAR SelectedDay = DAY(SelectedDate)
VAR SelectedMonth = MONTH(SelectedDate)
RETURN
    IF(
        DAY('DateTable'[Date]) = SelectedDay && 
        MONTH('DateTable'[Date]) = SelectedMonth,
        1,
        0
    )

     

    Set the filter condition to show values where IsSameDayAndMonth is 1

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    it is not working still basically my  coonection is many to many will be a problem because fact is direct table date table is in importb and my date table for slicer is not marked as date table also

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago
     
    I’m working on a Power BI report where I have a date slicer based on a ‘Date’ column from a table. When I select a date from the slicer, I want the report to display all dates that fall on the same day and month across different years.

    For example, if I select ‘5-31-2011’ from the slicer, the report should display ‘5-31-2011’, ‘5-31-2012’, ‘5-31-2013’, and so on. Essentially, I need rolling dates based on the selected day from the selected month.

    I understand that I need to write a DAX expression to achieve this, but I’m not sure how to go about it. I would appreciate any guidance or sample DAX expressions that could help me achieve this functionality. Please note that I would like to implement this without using my specific table and column names.

    Thanks in advance for your help!
    many to many relationship