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 ...
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. 

v-yalanwu-msft's avatar
v-yalanwu-msft
Icon for Community Support rankCommunity 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.