Measure error on rolling 12 month calculated column

[Anonymous]

Hi Experts

I cannot work out where i am going wrong with the following rolling 12 months measure. See calculate column measure i would like to keep the current measure and amend if possible...

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

Sample Data

https://www.dropbox.com/s/e280kz8rl9x0i4u/SSAMPLE.pbix?dl=0

[lbendlin]

You mention that you want a measure but your sample PBIX has a calculated column.  Please clarify.

The source data is a bit confusing. Can you maybe show the expected result for row 8 ?

[Anonymous]

Apologies i want to keep the calculated column measure....result for row 8 is 64.73

[Anonymous · Accepted solution]

Hi @Anonymous ,

The result for row 8 should be 64.15%.

(81,788,374+94,393,580+98,235,790)/(135,605,381+137,602,264+154,585,590)=0.6415

Column =
VAR _res =
    CALCULATE (
        SUM ( 'Llo_DIR_Data'[Result] ),
        FILTER (
            'Llo_DIR_Data',
            [Date] <= EARLIER ( Llo_DIR_Data[Date] )
                && [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
        )
    )
VAR _apps =
    CALCULATE (
        SUM ( 'Llo_DIR_Data'[Apps in Month/Term] ),
        FILTER (
            'Llo_DIR_Data',
            [Date] <= EARLIER ( Llo_DIR_Data[Date] )
                && [New Month] = EARLIER ( Llo_DIR_Data[New Month] )
        )
    )
RETURN
    DIVIDE ( _res, _apps )

I filtered out the blanks in the Power Query editor beforehand.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.