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

 

MonthTable.pngProjectTable.png

 

I built a relationship between the both table:

RelationShip.png

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?

Need Help

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident 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