Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

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 tables which would have separate tables calculating, Total Completed Audit This month,  last month, last quarter which would look something like:




Then I would just reference column 2 from each table adding it to a table on the main dashboard.


Excel reports would look like this:



Power BI I have got this far:



Relevant Fields from "Master Report"

"Actual Finish" 

"Assigned To"


I tried creating a new column with this formula:



Completed This Month =
SUM ( 'Master Report'[ActualFinish] ),
'Master Report'[ActualFinish].[Date],
(MIN ( 'Master Report'[ActualFinish] ) -30),
MIN ( 'Master Report'[ActualFinish] )



This is not working. This date only appears once in the ActualFinish field.



Does anyone have any ideas on how to achieve what I am after? 


Community Support
Community Support

Hi @DMB1 ,


According to your requirements, you could refer to this blog:


Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
Super User
Super User


Share some sample data and expected output with a clear explanation of what and how the calculation should be done. 
The Completed This Month formula is to calculate the past 30 days total of Actual/Finish. 


Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

Did I answer your question? Mark my post as a solution! and hit thumbs up

Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Frequent Visitor

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.




Frequent Visitor

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 =
    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.

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.