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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
domiowa
Frequent Visitor

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?

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

 

Share the dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-piga-msft
Resident Rockstar
Resident Rockstar

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.

 

1.PNG

 

Hope it can help you!

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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