Reporting based on Interval of two Date

[todor-dk]

Let's say we have a table with students at a school. It contains two columns, Start and End, indicating when the student started at the school and when he ended attending the school.

Start	End	Gender
2015-09-01	2020-05-26	Male
2016-08-25	2018-06-02	Female
2018-10-22		Female

This is obviously a simplified example, but my challenge is:

• I want a graph that tells me the number of male/female students (absolute or relateive)
• Distributed by year, with the possibility to drill-down the quarter, month or day levels.

In the above example, the resulting data should look something like, preferably represented by a bar chart and the option to drill down the date dimension:

	2015	2016	2017	2018	2019	2020	2021	2022
Female		1	1	2	1	1	1	1
Male	1	1	1	1	1

PS: I am aware that I can create a fake end column to simplify calculations, in the form of: CalculatedEnd = IF(ISBLANK(Table[End]), NOW(), Table[End])

[V-lianl-msft · Accepted solution]

Hi @todor-dk ,

Try to create a measure like below:

ActiveStudents = CALCULATE(COUNTx(FILTER(Students, Students[Start] <= MAX('Dates'[Date]) && (Students[End] >= MIN('Dates'[Date])||ISBLANK(Students[End]) )), Students[Gender]), CROSSFILTER(Students[Start],'Dates'[Date],None))

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

[amitchandak]

@todor-dk , I think one of these two blogs of mine, should help

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

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[todor-dk]

Hi,

I understand the general idea that a calendar table is needed and that a measure is used to calculate whatever is needed.

However, I have some issues understanding where to create the measure.

If we extend my example from above, I've create a table named Dates, which has one column Date one record per day in the time interval of interest.

I've create relations between the Start and End and Dates[Date], both are inactive.

Then, I've create a measure on the Dates table called ActiveStudents. It looks like this:

ActiveStudents = CALCULATE(COUNTx(FILTER(Students, Students[Start] <= MAX('Dates'[Date]) && Students[End] >= MIN('Dates'[Date]) ), Students[id]), CROSSFILTER(Students[Start],'Dates'[Date],None))

I must admit, I do not fully understand what this does. I understand the inner part, the COUNT and FILTER, but not CALCULATE and CROSSFILTER.

If I display Dates as a table, I get a date and the number of active students for that date. This also works if I display the dates aggregated at the month, quarter or year level.

But what I need is to be able to do more fancy statistics on the Studen table, for example total number of students, by gender or other colmns that exists in my real scenario. My suspicion is that the measure is not defined the correct place. Can you pls. comment.