Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
@Anonymous , Refer my blog on similar topic
@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 , Refer my blog on similar topic
here is a ondedrive link to the Pbix.file if you need it
@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
And for some reason it keeps failing when I fill in the exact same formula in the real file...
@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
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:
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
+ 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
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:
is your date dimension the same as what you give to me? I only corrected the Month year sorting
@amitchandak, I just did a quick check up with excel and had some different results. See screenshots, any idea why?
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
@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?
Yes - Check the calendar in links
Sun to Sat week -https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Mon-Sun Week https://www.dropbox.com/s/a9xq913pgvuzg2x/sales_analytics_weekWiseMon_sun.pbix?dl=0
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:
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!
Yep should be the same. Active/current opportunities is working so?
**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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |