Static Yearly Timelines

[LiamNeufeld]

Hello, 

I have a sort of unique challenge that I am hoping to get some support on. I have a list of reporting deadlines which have static due dates. I would like to add these timelines to my PowerBI dashboard as a guideline for my team. The problem that I am facing is that some of the timelines extend slighly past the current year. For example a report is requested in September of 2023 but due by March 2024. This is a problem as I wanted to write a quick DAX query to go off of the current year but I cant seem to add or subtract a year to reflect these altered due dates. For context I have an excel sheet with the timelines that I am pulling into PowerBI then showing via a Gantt Chart. 

Any advice is welcome, I am fairly new to PowerBI and DAX so it is entirely possible that I am missing something. 

Thanks!

[LiamNeufeld]

Here is some dummy data, I split the chart into three columns one for milestones, one for description, and one for the date (Based off of the milestone). 

The outliers occur when the dates extend past 2024. My initial thought was to write a dax query to just go off of the current year when viewing as these timelines are repeated yearly. However with the outliers this approach does not work. 

[v-shex-msft · Accepted solution]

HI @LiamNeufeld,

I'd like to suggest you write a measure expression to check records and return flag, then use it on the 'visual level filter' to filter records who matched with conditions.

Sample formula: check the current report group of records if they include records that match with configured conditions.

Flag =
VAR msType = "Requested Date"
VAR YearFilter = 2024
VAR currDesc =
    SELECTEDVALUE ( Table1[Description] )
VAR List =
    CALCULATETABLE (
        VALUES ( Table1[Description] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            [milestone] = msType
                && YEAR ( [Date] ) = YearFilter
        )
    )
RETURN
    IF ( currDesc IN List, "Y", "N" )

Regards,

Xiaoxin Sheng

[v-shex-msft]

Hi @LiamNeufeld ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng