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.
Hi,
I am struggling with a painful date issue ... I have a dataset made of
- project number
- date of creation (day-month-year)
- date of closing (day-month-year)
I would like to analyze, per months, how many projects are opened and how many are closed (combined bar chart per months (ordered chronologically) : for each month, one bar for number of open projects, one for number of closed projects)
- First, I am definitely unable to make it, especially as I can't found how to set a "global timeline" on X axis
- Second, I would like to set a slicer on global date. I mean, for the moment, if a set a slicer on creation date, I then exclude projects closed in this sclice but opened before ...
Thanks a lot for your help
Solved! Go to Solution.
Hi,
Thanks you both for your answers. I finally find a way to cope with this situation with something closed to what you suggested @v-yalanwu-msft
As I am working with an excel sheet as data source, I set a new tab with 1 period of time per line, and count (with formulas) how many projects opened and how many closed. Then I visualize these new fields in Power Bi. It works well but I lost all other project caracteristics. I guess your solution enable you to keep filtering on project type, etc and still get the monthly count.
Hi, @LoLo ;
Congratulations on the solution to your answer, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
Thanks you both for your answers. I finally find a way to cope with this situation with something closed to what you suggested @v-yalanwu-msft
As I am working with an excel sheet as data source, I set a new tab with 1 period of time per line, and count (with formulas) how many projects opened and how many closed. Then I visualize these new fields in Power Bi. It works well but I lost all other project caracteristics. I guess your solution enable you to keep filtering on project type, etc and still get the monthly count.
Hi, @LoLo ;
You could create a new date table according to open date and close date, then create two measures about open and close count .as follows:
1.create a new date table as x-axis.
Date = CALENDAR(MIN('Table'[creation date]),MAX('Table'[closing date]))
2.create two measures:
opencount = CALCULATE(COUNT('Table'[project number]),FILTER('Table',[creation date]<=MAX('Date'[Date])&&[closing date]>=MAX('Date'[Date])))
closecount = CALCULATE(COUNT('Table'[project number]),FILTER('Table',[closing date]<MAX('Date'[Date])))
The final output is shown below:
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@LoLo
Take a look at these two Quick Measures as I think you want something like them.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
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 |
---|---|
106 | |
94 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |