I need to automatically filter/group my data in a certain way, but I´m not sure it´s possible:
I have a dataset with multiple projects, each one with a "TARGET DATE"
I have a "DATE" column which basically is my report date and intended X Axis.
What I basically do is repeat the project list for every new report date.
The list can be bigger or smaller each month and the data can have small differences, but my TARGET DATE will be the same.
I want to create a table/filter my data in a way that each report date only has projects with a TARGET DATE from the start of the year up to the month of the report date. That gives me a cumulative list of projects by TARGET DATE.
DATE 01/01/2018: all projects with target date in January 2018
DATE 02/01/2018: all projects with target date from January 2018-February 2018
DATE 03/01/2018: all projects with target date from January 2018-March 2018
DATE 04/01/2018: all projects with target date from January 2018-April 2018
DATE 05/01/2018: all projects with target date from January 2018-May 2018
DATE 06/01/2018: all projects with target date from January 2018-June 2018
and so on.
The sample Excel file is in the link below. I have a sheet with the original data to be transformed in Power BI and another sheet with what the list would look like after doing what I want.
Can anyone help?