Forum Discussion

AndreaRoth's avatar
AndreaRoth
New Member
1 year ago
Solved

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....
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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.

anilelmastasi's avatar
anilelmastasi
Super User
1 year ago

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]))

RETURN
CALCULATE (
    CONCATENATEX ( Contracts, Contracts[contract_number], ", " ),
    USERELATIONSHIP ( Contracts[booking_date], DimDate[date] ),
    YEAR ( Contracts[rental_date_begin] ) = SelectedYear,
    MONTH ( Contracts[rental_date_begin] ) = SelectedMonth
)