Creating a count via lookup on another table

[matthewwylie]

I have a Power Bi report containing two tables

table 1 - Licences table

table 2 - a date table

I created this with:

Date table = CALENDAR(MIN(Licences[Start date]), MAX(Licences[Start date]))

I want to add a measure to the date table that shows how many licences were active on each date

i.e

count licence number (no. licences) from the licence table where

date table.date >= licences.start date

date table.date <= licences.expiry date

[Anonymous · Accepted solution]

Hi @matthewwylie ,

Please refer this measure.

measure =
CALCULATE (
    COUNT ( 'Licences'[licence number] ),
    FILTER (
        ALLSELECTED ( 'Licences' ),
        'licences'[start date] <= SELECTEDVALUE ( 'date'[date] )
            && 'licences'[expiry date] >= SELECTEDVALUE ( 'date'[date] )
    )
)

Best Regards,

Jay

[amitchandak]

@matthewwylie , seems like very similar to my HR blog

refer

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