Show Item as Active if given date falls between two dates

[TylerBrown]

Hi all, I'm very new, working on building reporting for a college.

Right now I have a table with a list of courses, each has an associated instructor name, class start date, and class end date.  Ultimately what I want to end up with is a list of active teachers on a given date (or date range).  This would allow me to select "next week" or "today" and see who is in class.

I've been able to achieve something similar with slicers - but I can only filter based on class start date - so I can tell who started class, but not necesariliy who is still in class (our classes start/stop all the time). 

Thoughts?  I've been teaching myelf DAX so simple terms are appreciated.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Also, share the underlying Excel file (from where you would have got data into the PBI file) so that i can connect to the Excel data source and carry out some M code transformations.

[v-shex-msft]

Hi @TylerBrown,

I'd like some sample data with expect result to test and coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[TylerBrown]

Here's what I have been able to do with slicers (near the upper left), one referencing the start date, one referencing the end date.  It feels like there should be a more elegant way to identify if a class is active.  I have a sample of data to share, but I don't see an upload option.  How do I best share it?

Thank you for your time and effort.

[v-shex-msft]

Hi @TylerBrown,

I think you need a calendar table with not has relationship to current table,then you can use following formula to create a measure and drag it to visual level filter from table visual to filter 'Y' result records:

In Selection Range =
VAR currStart =
    MIN ( Table[Section Start] )
VAR currEnd =
    MAX ( Table[Section End] )
RETURN
    IF (
        currStart IN ALLSELECTED ( Calendar[Date] )
            && currEnd IN ALLSELECTED ( Calendar[Date] ),
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng