Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
AndreaRoth
New Member

Many date fields

Hi everyone,

I want to see how many contracts are active in a specific month.

My data looks like that:

contract_numberrental_date_beginrental_date_endBooking_date
565124.01.202524.07.202501.01.2025
565206.12.202403.06.202501.12.2024
565306.02.202504.08.202501.02.2025
565428.02.202528.08.202520.02.2025
565509.01.202529.06.202527.12.2024

 

I also have a dim_calendar which looks like that:

DateMonthCalendar YearMonthName
01.02.202522025Feb
02.02.202522025Feb
03.02.202522025Feb
04.02.202522025Feb

 

 

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

1 ACCEPTED SOLUTION
danextian
Super User
Super User

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

danextian_0-1738759797497.png

Note: You will need a disconnected dates table.

Please see the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

View solution in original post

5 REPLIES 5
v-xinc-msft
Community Support
Community Support

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

anilelmastasi
Resolver II
Resolver II

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
)
danextian
Super User
Super User

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

danextian_0-1738759797497.png

Note: You will need a disconnected dates table.

Please see the attached pbix.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.
MattiaFratello
Resolver III
Resolver III

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

MattiaFratello
Resolver III
Resolver III

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?

 

DateMonthCalendar YearMonthName
01.02.202522025Feb
02.02.202522025Feb
03.02.202522025Feb
04.02.202522025Feb

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.