PowerBI DAX How to get records with selected values and startdate and enddate

Question

I have 4 selected values connected to dimDate and dimDate2 (copy)

• dimdate(startyear,startmonth,
• dimdate2 (endyear, endmonth)

connected to 2 columns startdate and enddate in another table

Here is the data

I want to be able to filterout rows (keep) that are in the selected period (Green) and exclude rows that haven't been open between this selected values.

-The easy answer here is to use Filter on this visualobject with startdate and enddate but you cant do that becouse if dynamic values , and you cant use created table becouse the is a dynamic selected values, you can't use new column to create a filter becouse the dynamic selected value

Whats left to do? I dont know maybe messurement. I have tyed to figuere this out for months, i have got some answer that is not right.

The I have the logic in SQL but i dont know how i can filterout rows using PowerBI with DAX. I can solve it with the things i said above (Without dynamic values) that you cant use.

Please help me figure out how i can filterout rows from a table. You don't need to solve the logic that i can do, just how i get rid of rows with logics.

Answer 1

Working in HR, your business requirement here is almost verbatim with how we develop headcount measures. Headcounts, similarly need to be able to consider dynamic start and end dates, as applied via slicers.

Between your fact table and date-dimension table, establish two inactive relationships between your start and end date columns with the date column of your date table. (You only need one date table.) Once you have done that, you can create a measure similar to this one:

Active Timeframes = 
CALCULATE(
    (SOME EXPRESSION HERE), // e.g. COUNTROWS( 'Some Table' )
    FILTER(
        VALUES( 'Table'[Start Date] ),
        'Table'[Start Date] <= MAX( 'Date'[Date] )
    ),
    FILTER(
        VALUES( 'Table'[End Date] ),
        'Table'[End Date] >= MIN( 'Date'[Date] ) ||
        ISBLANK( 'Table'[End Date] ) // Can exclude if needed.
    )
)

While this example may not be 100% specific to your use case, it should be able to get you in the right ballpark. For more information on this implementation, see this YouTube video by Enterprise DNA.