cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Route217
Post Prodigy
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

Capture.PNG

 

Sample PBIX File

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

 

 

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

2 REPLIES 2
mahoneypat
Super User IV
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!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

Many thanks Pat,

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors