Three tables - count rows by calendar day

[cipriannsft]

Hi everyone,

I've been struggling for weeks to convert a complex Excel sheet into a PowerApps and PowerBI solution. While I've easily done the PowerApp bit I'm really struggling with the PowerBI report.

I've created a few very simple screenshots though the actual worksheet is way more complex.

These are the main tables

And this is what I need to produce in PowerBI

It's basically a daily count of students from the first list that are in active placement on that day by placement name. 

The excel formula is something like this:

=COUNTIFS(Students_List, $CellOfPlacement, Students_List!StartDateColumn,">="&DayCell, Students_List!EndDateColumn,"<="&DayCell)

I'm struggling to transalte the above into a PowerBI report 😞

[v-angzheng-msft]

Hi, @cipriannsft 

Could you share some dummy data and draw a simple picture to show your expected visual for further discussion?

refer:

How to provide sample data in the Power BI Forum 

How to Get Your Question Answered Quickly

Best Regards,
Community Support Team _ Zeon Zheng

[amitchandak · Accepted solution]

@cipriannsft , I think very similar to HR use case

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

You can avoid join to date table with start and end date if needed