Lookup in Custom Date Measure Table

Hi bkennedype

I think regardless of the exact method, you should use a lookup table, just like in your original screenshot.

Capturing all the conditions in formulas could be quite difficult to maintain.

For instance, you could use something like the Static Segmentation pattern with DAX calculated columns

https://www.daxpatterns.com/static-segmentation/

I've dummied up a pbix containing tables similar to your original screenshot here.

PBIX link

I did notice some Events didn't match particular rows of the DateMeasures table, but I'm sure that can be fixed.

An example calculated column is:

Year Measure = 
CALCULATE (
    SELECTEDVALUE ( DateMeasures[Year], "<config error>" ),
    FILTER (
        DateMeasures,
        Events[Events Date] >= DateMeasures[Start_Date]
        && Events[Events Date] <= DateMeasures[End_Date]
        && Events[Group] = DateMeasures[Group]
    )
)

Another option is joining tables in Power Query.

Regards,

Owen