Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
I want to see how many contracts are active in a specific month.
My data looks like that:
contract_number | rental_date_begin | rental_date_end | Booking_date |
5651 | 24.01.2025 | 24.07.2025 | 01.01.2025 |
5652 | 06.12.2024 | 03.06.2025 | 01.12.2024 |
5653 | 06.02.2025 | 04.08.2025 | 01.02.2025 |
5654 | 28.02.2025 | 28.08.2025 | 20.02.2025 |
5655 | 09.01.2025 | 29.06.2025 | 27.12.2024 |
I also have a dim_calendar which looks like that:
Date | Month | Calendar Year | MonthName |
01.02.2025 | 2 | 2025 | Feb |
02.02.2025 | 2 | 2025 | Feb |
03.02.2025 | 2 | 2025 | Feb |
04.02.2025 | 2 | 2025 | Feb |
The main relationship between those tables is a n:1 "Booking_date" to "date".
Now I want to display all contracts which were active in month 01.2025 by using two data cutters: Calender Year = 2025 & Month = Feb
Solved! Go to Solution.
Hi @AndreaRoth
Assuming that the period of activity is from rental start to end date, try the following measure:
Count by Time Period =
VAR StartDate =
MIN ( Dates[Date] )
VAR EndDate =
MAX ( Dates[Date] )
RETURN
COUNTROWS (
FILTER (
'Fact',
'Fact'[rental_date_begin] <= EndDate
&& 'Fact'[rental_date_end] >= StartDate
)
)
Note: You will need a disconnected dates table.
Please see the attached pbix.
Proud to be a Super User!
Hi @AndreaRoth ,
Did the replies above offered help you solve the problem, if it helps, you can consider to accept it as a solution so that more user can refer to, or if you have other problems, you can offer some information so that can provide more suggestion for you.
Best regards,
Lucy Chen
Hello Andrea,
You can use USERELATIONSHIP() function for your case.
You can use below code:
Hi @AndreaRoth
Assuming that the period of activity is from rental start to end date, try the following measure:
Count by Time Period =
VAR StartDate =
MIN ( Dates[Date] )
VAR EndDate =
MAX ( Dates[Date] )
RETURN
COUNTROWS (
FILTER (
'Fact',
'Fact'[rental_date_begin] <= EndDate
&& 'Fact'[rental_date_end] >= StartDate
)
)
Note: You will need a disconnected dates table.
Please see the attached pbix.
Proud to be a Super User!
Or maybe your issue is that booking_date doesn't mean that it was an active booking for that particular month? And you would like to have a second relationship maybe with column begin_rental_date?
In that case check the DAX USERELATIONSHIP() https://learn.microsoft.com/en-us/dax/userelationship-function-dax
Hi @AndreaRoth , sorry but I didn't get your problem.
Can't you use your dim_calendar table to achiave what you need to do?
Using Calendar Year and Month Name?
Date | Month | Calendar Year | MonthName |
01.02.2025 | 2 | 2025 | Feb |
02.02.2025 | 2 | 2025 | Feb |
03.02.2025 | 2 | 2025 | Feb |
04.02.2025 | 2 | 2025 | Feb |
User | Count |
---|---|
121 | |
69 | |
67 | |
57 | |
50 |
User | Count |
---|---|
176 | |
83 | |
69 | |
65 | |
54 |