Hi,
I am quite new to PBI.
I am trying to show the Average of “Actual Duration” for Previous Month in a table “VMC based on Project Completion Date”. I need a dynamic measure.
As the date column does not show every day (meaning a row is created if there was activity that day) my understanding was that I need a Calendar Date table to use PREVIOUSMONTH or DATEADD. I have created a table "Dates" with a column "Date" from 1/01/2018 to 31/12/2021.
I have created relationship between two tables.
This is the measure I am trying to create:
AVG in PM = CALCULATE(AVERAGE('VMC based on Project Completion Date'[Actual Duration]),PREVIOUSMONTH(Dates[Date]))
I get blank result.
I also need to create a measure showing the YTD result in a Previous Month.
This is what I wrote:
Cycle Time = CALCULATE(AVERAGE('VMC based on Project Completion Date'[Actual Duration]),DATEADD(Dates[Date].[Date], -1,MONTH))
It does show the Average of 154 which is a current YTD. But as it is April now and I had no transaction for April I could not say if the measure shows YTD for March.
However, when I checked what was YTD result two months ago.
Cycle Time = CALCULATE(AVERAGE('VMC based on Project Completion Date'[Actual Duration]),DATEADD(Dates[Date].[Date], -2,MONTH))
The result was still the same 154.
Any help will be appreciated.
Evgeny
