Filter Date Table/SLicer

[Anonymous]

I have 3 queries that I am using to create a Project Management report.

1. 'Spent' is a report pulled from Oracle that contains data from all projects from the last 8 years or so.  This report only includes data for money that has been invoiced or spent.
2. 'Committed' is a report from Oracle that contains data from all projects like above, but only includes data with money that has not been spent.
3. 'Budget' is an excel sheet that is used to define an individual projects budget and forecast data.

I built the report based on using only excel spreadsheets.  Both the 'Spent' and 'Committed' excel sheets were exported and already filtered for a specific project.  We will be updating the report to use the actual Oracle data so that we no longer have to download/export data to update the report.  In doing so, I will now need to filter the 'Spent' and 'Committed' data by Project Number.  I am doing some testing with excel sheets that contain multiple projects data.  I have successfully filtered the entire report using a Project Number slicer.  However, I now would like to filter the "timeline" filter to show only dates relevant to the project selected, not all projects in the entire Oracle database.  The "timeline" slicer is created from separate date columns from the 'Budget' and 'Spent' queries.  This date table is used to track the spend and budget/forecast over time.

There are existing columns that define each projects start date 'Spent'[PROJECT START DATE] and end date 'Spent'[PROJECT END DATE].  I have successfully created two measures that display the correct start date and end date for the selected project.

Start Date = 
minx(FILTER(Spent, [PROJECT] = SELECTEDVALUE('Project Table'[PROJECT])), Spent[PROJECT START DATE])

End Date = 
MAXX(FILTER(Spent, [PROJECT] = SELECTEDVALUE('Project Table'[PROJECT])), Spent[PROJECT END DATE])

I then tried countless variations of using these dates to create a "Project Date Table" that line up with these dates.  The simplest is below, but does not filter based on the measures, it shows the dates from every project.  Below, the 'Table'[Date] is a defined date table that starts at the beginning of the first project and ends at the last date of the last project.

Date Table = 
    DATESBETWEEN('Table'[Date], 'Spent'[Start Date], 'Spent'[End Date])

 Project number slicer.Timeline slicer.Attempt at Project Dates table.Correct project end date.Relationships.Correct project start date.

[AllisonKennedy · Accepted solution]

You will need to create a MEASURE that finds the start and end date for each project after slicer selections. A table built using DAX will never update based on slicer selection. This is because of the order of operations in Power BI. Power BI order of operations is basically as follows:

Raw data source updates/changes
Power Query operations (M code)
DAX tables and columns
Slicers
DAX measures

When you click Refresh, it will go back to the Raw data source and check for updates, apply the query changes from Power Query, then calculate that DAX tables and columns and THEN apply the slicer selections.

If you want the DimDate table to be filtered by the project values, try adding a measure to it as a filter. Similar to this post from Reza Rad on filtering slicers with slicers:
https://radacad.com/one-dimension-filters-another-dimension-in-power-bi/?ref=818

[Anonymous]

Hi Allison,

Thank you!!!  I should have done some more testing first.  I had the solution, but overthought it...  I thought the measure I was using would create a never ending project, as the "cumulative budget" measure will always have a value greater than 0 after project initiation.  Everything looks to work as I want though.

[AllisonKennedy]

@Anonymous You're welcome!
Glad it's working as you want - everything seems so simple once you know the answer. 🙂