Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
matthewwylie
New Member

Creating a count via lookup on another table

I have a Power Bi report containing two tables

 

table 1 - Licences table

matthewwylie_0-1647448725147.png

table 2 - a date table

I created this with:

Date table = CALENDAR(MIN(Licences[Start date]), MAX(Licences[Start date]))
matthewwylie_1-1647448780770.png

 

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

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
Super User
Super User

@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...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.