cancel
Showing results for
Did you mean:
Post Prodigy

## TRICKY - Rolling 12 months Measure error

Hi Experts

I cannot work out where i am going wrong with the following rolling 12 months measure. See calculate column measure.

For Jan 19 The result is correct 60%

For Feb 19 The result is worng and should be the Sum of (81,788,374+94393580) / (135,605,381 + 137602264) = 64%

not (94393580) / (137602264) which the measure is returning.

See expected result in excel image

Sample PBIX File

1 ACCEPTED SOLUTION
Super User IV

This column expression seems to work but two questions.  Why not do this as a measure instead?  And is there a better way to model your data, to avoid having all the duplicate values in your table?

New column =
VAR enddate = Llo_DIR_Data[Date]
VAR startdate =
EOMONTH ( enddate-6 ) + 1
VAR result =
CALCULATE (
AVERAGEX (
DISTINCT ( Llo_DIR_Data[Date] ),
CALCULATE (
DIVIDE (
AVERAGE ( Llo_DIR_Data[Result] ),
AVERAGE ( Llo_DIR_Data[Apps in Month/Term] )
)
)
),
ALL ( Llo_DIR_Data ),
FILTER (
ALL ( Llo_DIR_Data[Date] ),
Llo_DIR_Data[Date] >= startdate
&& Llo_DIR_Data[Date] <= enddate
)
)
RETURN
result

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

2 REPLIES 2
Super User IV

This column expression seems to work but two questions.  Why not do this as a measure instead?  And is there a better way to model your data, to avoid having all the duplicate values in your table?

New column =
VAR enddate = Llo_DIR_Data[Date]
VAR startdate =
EOMONTH ( enddate-6 ) + 1
VAR result =
CALCULATE (
AVERAGEX (
DISTINCT ( Llo_DIR_Data[Date] ),
CALCULATE (
DIVIDE (
AVERAGE ( Llo_DIR_Data[Result] ),
AVERAGE ( Llo_DIR_Data[Apps in Month/Term] )
)
)
),
ALL ( Llo_DIR_Data ),
FILTER (
ALL ( Llo_DIR_Data[Date] ),
Llo_DIR_Data[Date] >= startdate
&& Llo_DIR_Data[Date] <= enddate
)
)
RETURN
result

Pat

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Post Prodigy

Many thanks Pat,

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.