Calculated Columns with Relative Date Filter

[DMB1]

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 =
CALCULATE (
SUM ( 'Master Report'[ActualFinish] ),
DATESBETWEEN (
'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? 

[v-eachen-msft]

Hi @DMB1 ,

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

https://www.poweredsolutions.co/2019/07/23/fill-dates-between-dates-with-power-bi-power-query/

[Fowmy]

@DMB1 

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

[DMB1]

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.

[DMB1]

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.