Forum Discussion

DMB1's avatar
DMB1
Frequent Visitor
6 years ago

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

 

Need Help

4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    6 years ago

    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's avatar
      DMB1
      Frequent Visitor
      6 years ago

      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's avatar
        DMB1
        Frequent Visitor
        6 years ago

        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.