Forum Discussion

domiowa's avatar
domiowa
Frequent Visitor
8 years ago

Count if between 2 tables

Hi everyone, I have a problem to do something very simple on excel but so hard on power BI I have a project table with my project Name, starting date, excepted ending date and ending date So I'd l...
Need Help
v-piga-msft's avatar
v-piga-msft
Icon for Resident Rockstar rankResident Rockstar
8 years ago

Hi domiowa,

 

From your description, if I understand your case correctly that you want to count the month which start at month M and fished at M+1.

 

You could create the measure below to count it.

 

Measure =
COUNTROWS (
    FILTER (
        'Project',
        (
            YEAR ( 'Project'[Starting Date] ) = YEAR ( MAX ( 'Date'[Month] ) )
                && MONTH ( 'Project'[Starting Date] ) = MONTH ( MAX ( 'Date'[Month] ) )
        )
            && YEAR ( 'Project'[Endingdate] ) = YEAR ( MAX ( 'Date'[Month] ) )
            && MONTH ( 'Project'[Endingdate] )
                = MONTH ( MAX ( 'Date'[Month] ) ) + 1
    )
)

The picture of the result is below.

 

 

Hope it can help you!

 

Best Regards,

Cherry