Show Occurences this month across multiple date columns

[carterd366]

I am working on a project with a number of dates in separate columns, signifying the workflow. I would like to display a dashboard of what has happened this month, listing the projects this month, along with its current status and status last month.

I am not sure how to go about this. I currently have separate tables for each stage; with Project Workflow ID as the unique ID relating the main Project Details table (through a 1-1 relationshop with both cross-direction) and a date column (with a 1-* relationship with both cross-direction).

I have a basic Matrix visualisation displaying the project, along with each phase column. If I use the Relative Date Filter for each phase column, this filters out all of the other phase columns regardless of whether or not they have a date this month. 

I have created a "ThisMonth" column on the date table (Date Key) which has a 1 when it is within the current month and all others have a 0, but this does not seem to work when used as a visualisation filter.

Below is my date table:

 

Below you can see a small (but key) part of the relationships I have.

The Project Name is taken from the Project Details table, which is connected to Project Workflow table with a 1-1 relationship through Project Code.

The Project Workflow table is then connected to a variety of tables for each phase of the process, here you can see both Expected and Actual Collaborator Access tables, as well as Analysis Start. These are connected through a 1-1 relationship on Project Workflow ID.

I initially connected these with a 1-* relationship to Date Key (the date table), but as this didn't work, I allowed Power BI to automatically find other relationships, so as you can see the Analysis Start table is now connected to most of the other process phase tables through matching Project Workflow ID rows. I don't think this should be the case, but tried it nonetheless.

Any help would be appreciated, as this would be a very useful report for how projects have progressed through the month.

[v-shex-msft]

HI @carterd366,

>> would like to display a dashboard of what has happened this month, listing the projects this month, along with its current status and status last month.

I'd like to suggest you use below formula to generate the detail date table, then build relationship between original table and new table.

Reference link:

chart with start and end date

After these steps, you can use date table and original records to create visuals and use slicer to control filtered date range.

Regards,

Xiaoxin Sheng

[carterd366]

Hi @v-shex-msft,

I'm not sure what the details table does in the example, or how it applies to my problem? I have added more details to my post in the hope it will make things clearer.

Regards

Dave

[v-shex-msft]

HI @carterd366,

According to your screenshots, I found your have stored data in multiple tables, right?( they may used as start date and end date)

Since power bi can't direct option with date range who stored in different columns, my suggestion is merge table data to generate one table include each day of specific date range.

workflow table -> table1(workflow id, start date)
               -> table2(workflow id, end date)
↓
workflow table -> merge table(workflow id, start date, end date, detail date(each date of previous date range))

After these steps, you workflow column can correspond to every day of date range.

Regards,

Xiaoxin sheng

[carterd366 · Accepted solution]

Thanks @v-shex-msft. I don't think what you suggested would have worked, as the columns have different orders for different projects. However, I used your "one table" idea to add all dates to a single table and unpivot the columns, so that only one date column was being used.

Thanks for your help.

Dave