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
Anonymous
Not applicable

Active opportunities

I have another question, we do have these opportunities that have a start and end date. We would like to have a visual (barchart) X-axis = months jan to dec. Y axis = count of possible opportunity.

Opportunities       -->       startdate       -->       enddate
Opp1                                01/05/2020                31/12/2020
Opp2                                24/04/2020                31/12/2020 

Opp3                                01/03/2020                31/12/2020 
Opp4                                01/03/2020                31/12/2020   
 
Problem here is that it would only count the number of opportunities on the months: march(03) / april (04) / may (05). while these projects do are also active on the months june, july, aug, ... Dec
 
Link to sample pbix file
 
Any ideas?
2 ACCEPTED SOLUTIONS

@Anonymous , No worries you can accept two solutions. Please find the attached file. As I do not see any null end date , omitted that clause

View solution in original post

20 REPLIES 20
amitchandak
Super User
Super User
Anonymous
Not applicable

@amitchandak 

 

here is a ondedrive link to the Pbix.file if you need it

 

https://1drv.ms/u/s!AvlpOfUDGFD5tBA9z0mIrFpx61tx?e=npZLNq

@Anonymous , No worries you can accept two solutions. Please find the attached file. As I do not see any null end date , omitted that clause

Anonymous
Not applicable

And for some reason it keeps failing when I fill in the exact same formula in the real file...

Anonymous
Not applicable

@amitchandak seemed to work, but can you explain me why it only seems to work with the "YearMonthShort" date column and not with the others?

 

thanks!

@Anonymous , anything that can lead to a date it will work. Like when I selected Month No it did not work. But when I selected year with it it will work.

When you select month it will get max date in 2025.  I test a Year filter and moved the date back to 2020, I got data.

The same is true for weekdays and others. Remember max date needs a container so when you give month year it end date of the month, but when you give month there in no year so max will go till the end of the calendar for that month for Max.

 

Hope this will explain

Anonymous
Not applicable

Okay thanks alot @amitchandak it seems to be working now. For some reason december isn't giving any data in my file tho 😅. Also new opportunities and terminated opportunities isn't working yet. Would be really nice if u could provide these formulas in the sample file as well. 

 

New Opportunities: 

CALCULATE(COUNT(opportunities_per_employee[name]),USERELATIONSHIP(opportunities_per_employee[new_estimatedprojectstartdate],'Date'[Date]) )
 
Terminated opportunities:
CALCULATE(COUNT(opportunities_per_employee[candidate]),USERELATIONSHIP(opportunities_per_employee[new_estimatedprojectenddate],'Date'[Date]),not(ISBLANK(opportunities_per_employee[new_estimatedprojectenddate])))

 


errors.PNG

Anonymous
Not applicable

and @amitchandak any idea how you would hande a drill trhough from that graph? I would like to be able to get some more specific info about opportunities by doing a drill trhough. I was thinking something like: + check my previous post

 

DT button.PNGDT.PNG

 

+ check my previous post

@Anonymous , File with open and closed attached

 

For Drill through:https://docs.microsoft.com/en-us/power-bi/desktop-drillthrough

https://docs.microsoft.com/en-us/power-bi/desktop-cross-report-drill-through

 

If Drill Through do not resolve using the above start a new topic

Anonymous
Not applicable

For some reason I end up getting this using your formulas for open & closed, and I don't egt why I am missing december data:

 

New opportunity:

CALCULATE(COUNT(opportunities_per_employee[name]),USERELATIONSHIP(opportunities_per_employee[new_estimatedprojectstartdate],'Date'[Date]))
 
Terminated opportunity:
CALCULATE(COUNT(opportunities_per_employee[name]),USERELATIONSHIP(opportunities_per_employee[new_estimatedprojectenddate],'Date'[Date]))
 
Erroropenclosed.PNG

is your date dimension the same as what you give to me? I only corrected the Month year sorting

 

Screenshot 2020-04-21 20.01.58.png

 

Anonymous
Not applicable

@amitchandak, I just did a quick check up with excel and had some different results. See screenshots, any idea why?

 

Excel data.PNGResults Excel.PNG

I do not think I have the same data as excel. But we can have one modification

Current Opportunity = CALCULATE(COUNTX(FILTER(Opportunities,Opportunities[new_estimatedprojectstartdate]<=max('Date'[Date]) && Opportunities[new_estimatedprojectenddate] >=min('Date'[Date])),Opportunities[name]),CROSSFILTER(Opportunities[new_estimatedprojectstartdate],'Date'[Date],None)) 

 

This means it will be considered as current if it closed this month. Check >=Min in place >=Max for end date

Anonymous
Not applicable

@amitchandak do you know a way how I can show week 1-52 on the x-axis instead of months? Probably need to addapt my date table?

Anonymous
Not applicable

Hi @amitchandak I have an additional question for this. Don't know if I should make a new topic for this cause then I have to explain everything again. But right now thanks to you I have the active opportunities summed up per month. this looks like this:

 

 

Here is a link to Sample data:

https://1drv.ms/x/s!AvlpOfUDGFD5tRpctDrmOCEy6aHF?e=4FYzZ2

Anonymous
Not applicable

Hi @amitchandak yes I provided the same data but with other names for privacy reasons. Your new fomula is more accurate tho! are you able to explain the formula as I have no idea what I am doing. Sorry If I am asking alot, you ahev been really helpfull already!

Anonymous
Not applicable

Yep should be the same. Active/current opportunities is working so?

Anonymous
Not applicable

**bleep** accidently accepted my reply as a solution.

 

@amitchandak I have a sample pbix file I want to share but how do I provide it?

 

Anonymous
Not applicable

Hi @amitchandak. I addapted your logic but it seems liek there is an error

 

Problem.PNGRS.PNGMeasure.PNG

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.