TRICKY - Rolling 12 months Measure error

[Anonymous]

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

https://www.dropbox.com/s/6uqpevfaqhxy0ye/sAMPLE.pbix?dl=0 

[mahoneypat · Accepted solution]

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

[Anonymous]

Many thanks Pat,