I have a measure for calculating average days open for open cases-
<code>Average Days open =
CALCULATE(AVERAGEX('cases',IF('Cases'[Case Status]<>"Closed",DATEDIFF('Cases'[Created On],TODAY(), DAY))))
</code>
<code>Average Days open =
CALCULATE(AVERAGEX('cases',IF('Cases'[Case Status]<>"Closed",DATEDIFF('Cases'[Created On],TODAY(), DAY))))
</code>
Average Days open =
CALCULATE(AVERAGEX('cases',IF('Cases'[Case Status]<>"Closed",DATEDIFF('Cases'[Created On],TODAY(), DAY))))
This is working, but I am trying to create a moving average measure for this. However this shows blank. Can anyone help me regarding this –
<code>MA Avg Days open for open cases =
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX('Date'[Date])
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE([Average Days open], Period)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithDate = MAX ( 'Date'[Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithDate, Result )
</code>
<code>MA Avg Days open for open cases =
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX('Date'[Date])
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE([Average Days open], Period)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithDate = MAX ( 'Date'[Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithDate, Result )
</code>
MA Avg Days open for open cases =
VAR NumOfMonths = 12
VAR LastCurrentDate = MAX('Date'[Date])
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE([Average Days open], Period)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithDate = MAX ( 'Date'[Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithDate, Result )