Forum Discussion
Many date fields
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
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.
5 Replies
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.
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 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
Hello Andrea,
You can use USERELATIONSHIP() function for your case.
You can use below code:
Contracts_Measure =VAR SelectedYear = VALUE(SELECTEDVALUE(DimDate[Year]))VAR SelectedMonth = VALUE(SELECTEDVALUE(DimDate[Month Calendar]))RETURNCALCULATE (CONCATENATEX ( Contracts, Contracts[contract_number], ", " ),USERELATIONSHIP ( Contracts[booking_date], DimDate[date] ),YEAR ( Contracts[rental_date_begin] ) = SelectedYear,MONTH ( Contracts[rental_date_begin] ) = SelectedMonth)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
