Forum Discussion
Calculated Columns with Relative Date Filter
I am working with porting a Excel monthly report over to Power BI. I am having difficulty doing relative date per column filtering on the power bi report. In excel I used a data tab/pivot ta...
The data looks like this:
I want to have 2 columns showing a count of status "Complete" separated by "assigned to" filtered by "Actual Finish" date to the last 30 days for column1 and 90 days for column2.
I want final data to be 2 columns in one table. showing Last 30 days, Last 90 days.
Hi Guys,
I am still having difficulty with this.
I have created 2 measures:
CompleteCount =
CALCULATE( COUNTROWS( 'Master Report' ), 'Master Report'[Status] = "Complete" )
I then made a new Quick Measure:
CompleteCount MTD =
IF(
ISFILTERED('Master Report'[ActualFinish]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
TOTALMTD([CompleteCount], 'Master Report'[ActualFinish])
)
The Problem is its basing the MTD on -30 days from the last populated date rather than from todays date.
In the screenshot below you can see 2 consultants are showing 10 and 1.
Both these consultants have no ActualFinish dates past May / July 2019. So I need to change the MTD measure to only calculate -30 from todays date.