Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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. 

4 Replies

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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. 

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    4 years ago

    Hi, Anonymous ;

    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.