Forum Discussion

byr10112's avatar
byr10112
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Previous Month's Average Score

I'm trying to create a visual where the current month's and the previous month's average scores are being displayed. I have a Fiscal Year calendar table that has all the date information. Below is the measure I tried to create, but the previous month's column is populating the current month's values:

 

Previous Month Average Score = 
VAR CurrentMonth = SELECTEDVALUE('Fiscal Calendar'[FM])
VAR CurrentYear = SELECTEDVALUE('Fiscal Calendar'[FY])
VAR MaxMonthNum = CALCULATE( MAX('Fiscal Calendar'[FM]) ,  ALL('Fiscal Calendar'))

RETURN
IF ( HASONEVALUE('Fiscal Calendar'[FM] ),

AVERAGEX(
    FILTER ( ALL('Fiscal Calendar'),
    IF( CurrentMonth = 1,
    'Fiscal Calendar'[FM]= MaxMonthNum && 'Fiscal Calendar'[FY]= CurrentYear -1,
    'Fiscal Calendar'[FM]= CurrentMonth-1 && 'Fiscal Calendar'[FY] = CurrentYear)),
    AVERAGEX( 'Advisor Score Weekly Med',AVERAGE('Advisor Score Weekly Med'[Wkly Score]))), BLANK())

 

This is what the current end table is looking like:

FMFYAverage ScorePrevious Month's Avg
12202178%78%
1202284%84%
2202282%82%

 

I'm trying to have this end table:

FMFYAverage ScorePrevious Month's Avg
12202178% 
1202284%78%
2202282%84%

 

Any changes I can make to my formula to achieve what I want?

Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi byr10112 ,

     

    You can try the following methods.

     

    Previous Month Average Score =
IF (
    SELECTEDVALUE ( 'Fiscal Calendar'[FM] ) = 1,
    CALCULATE (
        AVERAGE ( 'Advisor Score Weekly Med'[Wkly Score] ),
        FILTER (
            ALL ( 'Fiscal Calendar' ),
            [FM] = 12
                && [FY]
                    = SELECTEDVALUE ( 'Fiscal Calendar'[FY] ) - 1
        )
    ),
    CALCULATE (
        AVERAGE ( 'Advisor Score Weekly Med'[Wkly Score] ),
        FILTER (
            ALL ( 'Fiscal Calendar' ),
            [FM]
                = SELECTEDVALUE ( 'Fiscal Calendar'[FM] ) - 1
                && [FY] = SELECTEDVALUE ( 'Fiscal Calendar'[FY] )
        )
    )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

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

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    byr10112 , Taking rolling Avg of last two month

     

    Rolling 2 = calculate(AverageX(Values('Date'[MONTH Year]),CALCULATE(sum(Sales[Sales Amount]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,MONTH))

     

     

    Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
    https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi byr10112 ,

     

    You can try the following methods.

     

    Previous Month Average Score =
IF (
    SELECTEDVALUE ( 'Fiscal Calendar'[FM] ) = 1,
    CALCULATE (
        AVERAGE ( 'Advisor Score Weekly Med'[Wkly Score] ),
        FILTER (
            ALL ( 'Fiscal Calendar' ),
            [FM] = 12
                && [FY]
                    = SELECTEDVALUE ( 'Fiscal Calendar'[FY] ) - 1
        )
    ),
    CALCULATE (
        AVERAGE ( 'Advisor Score Weekly Med'[Wkly Score] ),
        FILTER (
            ALL ( 'Fiscal Calendar' ),
            [FM]
                = SELECTEDVALUE ( 'Fiscal Calendar'[FM] ) - 1
                && [FY] = SELECTEDVALUE ( 'Fiscal Calendar'[FY] )
        )
    )
)

     

     

    Best Regards,

    Community Support Team _Charlotte

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