Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi
I'm trying to replicate the following excel table in PowerBI:
The table displays the number of absent staff per month in the past 24 months.
The table uses the TODAY() and EOMONTH() excel formulae to generate a 'Month', 'End Of Month' and 'Start Of Month' column.
Then using the following formula it counts how many staff were absent (at any point) in each month:
Where Absence_Data2 is my source data of a list of staff with a start and end date for each staff absence period:
I've tried making a DateTable query in PowerBI to generate a 'Month', 'End Of Month' and 'Start Of Month' column in a powerBI matrix:
I've connected my DateTable query to the Absence_Data2 query as follows:
From here I'm not sure how to add an 'Absence Occurences' column to my matrix to count how many staff were absent in each month.
i.e. count how many absence date ranges of my Absence_Data2 query have dates that fall within the date range of each month of my DateTable query.
The source data and powerBI files are available via the follwoing link:
Absence_Occurences_AO_Sample_v5.xlsx
Absence_Dashboard_Sample_v1.pbix
Any help or guidance would be much appreciated!
Solved! Go to Solution.
Hi, @JFarq
Please try formula like:
calculated column:
Absence Occurences per person per month =
CALCULATE (
COUNT ( Absence_Data2[Start Date] ),
ALLEXCEPT (
Absence_Data2,
Absence_Data2[Start Date].[Month],
Absence_Data2[Start Date].[Year],
Absence_Data2[Forename And Surname]
)
)
Measure:
count of absence date =
CALCULATE (
COUNTROWS ( Absence_Data2 ),
FILTER (
Absence_Data2,
Absence_Data2[Start Date] >= MAX ( 'Calendar (UK?)'[StartOfMonth] )
&& Absence_Data2[Start Date] <= MAX ( 'Calendar (UK?)'[EndOfMonth] )
)
) + 0
(need change the data type of 'Calendar (UK?)'[StartOfMonth], 'Calendar (UK?)'[EndOfMonth] from 'Text' to 'Date')
Best Regards,
Community Support Team _ Eason
Hi, @JFarq
Please try formula like:
calculated column:
Absence Occurences per person per month =
CALCULATE (
COUNT ( Absence_Data2[Start Date] ),
ALLEXCEPT (
Absence_Data2,
Absence_Data2[Start Date].[Month],
Absence_Data2[Start Date].[Year],
Absence_Data2[Forename And Surname]
)
)
Measure:
count of absence date =
CALCULATE (
COUNTROWS ( Absence_Data2 ),
FILTER (
Absence_Data2,
Absence_Data2[Start Date] >= MAX ( 'Calendar (UK?)'[StartOfMonth] )
&& Absence_Data2[Start Date] <= MAX ( 'Calendar (UK?)'[EndOfMonth] )
)
) + 0
(need change the data type of 'Calendar (UK?)'[StartOfMonth], 'Calendar (UK?)'[EndOfMonth] from 'Text' to 'Date')
Best Regards,
Community Support Team _ Eason
Thank you very much v-easonf-msft.
Your measure works for me.
I just had to write it out with 'MANUAL End Date' in first line of the second argument of the FILTER() function:
count of absence date =
CALCULATE (
COUNTROWS ( Absence_Data2 ),
FILTER (
Absence_Data2,
Absence_Data2[MANUAL End Date] >= MAX ( 'Calendar (UK?)'[StartOfMonth] )
&& Absence_Data2[Start Date] <= MAX ( 'Calendar (UK?)'[EndOfMonth] )
)
) + 0
To get:
In Power BI it is best practice to use a calendar table.
All reports use dates so this is really important skill for you to learn
Check out the July 2025 Power BI update to learn about new features.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
User | Count |
---|---|
68 | |
64 | |
52 | |
39 | |
26 |
User | Count |
---|---|
80 | |
57 | |
45 | |
44 | |
35 |