cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LoLo
Regular Visitor

Date issue

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

1 ACCEPTED SOLUTION
LoLo
Regular Visitor

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. 

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

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.

LoLo
Regular Visitor

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. 

View solution in original post

v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1633416344339.png

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.

Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.