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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors