Count if between 2 tables

[domiowa]

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 like to nake some calculation with these data so this is an example, I'd like by month all the project which start at month M and which finished at M+1

So to do that, I built a second table: Month in it I put the month for I want to make the count

With these screenshots, I think everything will be easier for you

So I have two table one project and one month

I built a relationship between the both table:

After that, I'd like to count with a measure(or a column) my project which start the same month than the month in my date table and which are finished one month later so I thought to do that with:

Measure = CALCULATE(COUNT(Project[Starting Date]);('Date'[Month]= 'Project'[starting Date]);((DATEADD('Date'[Month];1;MONTH)='Project'[ending Date])))

I aslo tried it with a new column:

Column = CALCULATE(COUNT(Project[Starting Date]);('Date'[Month]= 'Project'[starting Date]);((DATEADD('Date'[Month];1;MONTH)='Project'[ending Date])))

I'm loosing my mind, why things which are so easy on excel are impossible on Power bi?

Thanks in advance for you help

Any idea of what I could do?

[Ashish_Mathur]

Hi,

Share the dataset and show the expected result.

[v-piga-msft]

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