Report Level Filter Based On Formula

[Lenin]

Hi,

     I have a table (TaskTimePhasedDataSet) that has time phased project data. The table measures project performance over time, akin to the "Earned Value Over time" report in Microsoft Project.

The table contains cumulative data calculated across the time span of the project (for eample "Actual Work" by day). Therefore if I filter the table rows it will effect my project performance calculations.

I want to only display data in the report based on a defined date (called the "Project Status Date") which is in my table (TaskTimePhasedDataSet). So I could have project performance data for all of March 2017 however I only want to display in the report data up to the "Project Status Date" say the 15th March 2017.

Is there any way within the report filters to do this?

Thanks

Lenin

[Anonymous]

Hi @Lenin,

Could you please share sample data of your table and post expected result here?

Thanks,
Lydia Zhang

[Lenin]

Hi Lydia,

            thanks for getting back. Please see screenshot below. The table represents project perofrmance data which has cumulative figures calculated for each day. The line chart reflects the data from the "TimeByDay" and "SPI" columns from the table. I also have a column in the same table (TaskTimePhasedDataSet) called "Project Status Date", highlighted in the matrix between the line chart and table.

I would like to be able to show data on the line chart up to the "Project Status Date". I cannot filter the table because all table values are used to calculated some metrics. I therefore was querying whethert here were some report level filters I could use that are driven by a formula.

Any help greatly appreciated

[Anonymous]

Hi @Lenin,

You want to show data of only 07/03/2017 and 06/03/2017 in Line chart, but want to show all March data in table, right? If that is the, create a slicer using your x-axis column, then enable ”Edit interactions” and select “None” for your table visual, this way, when you select value in the slicer to filter Line Chart to show data of 07/03/2017 and 06/03/2017, table visual will not be filtered. There is an example for your reference.

Thanks,
Lydia Zhang

[Lenin]

Hi Lydia,

              I was seeking to be able to show data on the line chart up to the "Project Status Date" which is set on my table. I wanted this to happen automatically i.e. when I select a project from my drop down the line chart only shows data up to the "Project Status Date".

Sorry if this is not clear.

Lenin

[Anonymous · Accepted solution]

Hi @Lenin,

You may need to create measure to calculate SPI when the TimeByDay equals to Project Status Date, then create Line Chart using the measure. You can share sample data of your tables or PBIX file for us to analyze.

Thanks,
Lydia Zhang