Showing active memberships in a table between dates from Date Slicer

[djheathy]

I have a Date Dimension and a Membership fact table. I have an active relationship between the Start Date of the membership and the Date in my Date Dimnsions

On my report I have a Date slicer based on the date from date dimension. I want users to be able to select a date range and see a list in a table of any memberships that were Active during that period (Started during, started before and ended during, started during and ended during or after).

I've successfully achieved this with a few Measures using code similar to this:

Number of active membships =

CALCULATE(
calculate(
countrows('memberships',
FILTER (
ALL('Memberships'),
[Membership Start Date] <= Max('Date'[Date])
&& ( [Membership End Date] >= Min('Date'[Date]) || isblank('Memberships'[Membership End Date]))

))
,CROSSFILTER('Memberships'[Membership Start Date],'Date'[Date],NONE))

What I really want to be able to do is add a visual level filter to my table where my measure =1, but this doesn't work and calculated columns aren't supported with this kind of dynamic logic.

I appreciate I could add two new slicers on the Membership start Date and Membership End Date, but I need to use the single date filter mentioned above as this affects other visualisations on the report. 

Any help would be greatly recevied.

Thanks

[v-xicai]

Hi @djheathy ,

You may create measure like DAX below.

Number of active membships =
VAR _SelectedDate =
    SELECTEDVALUE ( 'Date'[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( 'memberships' ),
        FILTER (
            ALLSELECTED ( 'Memberships' ),
            [Membership Start Date] <= _SelectedDate
                && (
                    [Membership End Date] >= _SelectedDate
                        || ISBLANK ( 'Memberships'[Membership End Date] )
                )
        )
    )

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[djheathy]

Hi Amy @v-xicai 

Thanks for the sample code. I tried adapting the code to use with @amitchandak HR analytics example using a between date slicer to try and filter down on employees that were active during the period selected, however it's not giving me the desired result.

My Active employee measure is defined as this:

Active Employee = 

Var _MINSelectedDate = 
CALCULATE ( MIN ('Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )

Var _MAXSelectedDate = 
CALCULATE ( Max ('Date'[Date] ), ALLSELECTED ( 'Date'[Date] ) )

Return

Calculate(
    COUNTROWS('Employee'),
    FILTER(
        ALLSELECTED('Employee'),
        [Start Date]<=_MAXSelectedDate
        && ( 
            [End Date] >=_MINSelectedDate
            || ISBLANK ('Employee'[End Date])
        )  
    )
)

I'm hoping to define a measure that I can then use as a Visual Level Filter, that will only count employees active during the period defined by the between date slicer. Thanks

[amitchandak]

@djheathy , refer this blog, very similar thing has been done

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[djheathy]

Amit, 

Thanks for the pointer. I hadn't come acorss that article.

What I really want is to add a table to your example and add employee details, select a period (or in my case a date range) and see 'active' records (Employees in your example).  

I've downloaded your .pbix, added a table and, but I'm only able to get it to show new hires. I can edit interactions and break the link betweent the slicer and table and add a visual level filter to the table and filter on 'current employee'=1, but that then will only give me current employees for the most recent period. no link between slicer and table, current employee =1 visual filterslicer and table linked

[parry2k]

@djheathy you should be adding this as a measure, not a column, and then use measure for the visual level  filter.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[djheathy]

Hi, 

I've created it as a measure, and works fine as a calculation, however, if I try and add that measure to my table (along with other columns from the fact table), I get the same value for each row. Therefore adding it as a visual level filter (>1) has no effect. 

I've tried disconnecting the fact and date dimension tables, with no success too.