Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

12 month rolling array MAX

I'm currently trying to figure out an 12 month array from every month to find the max value within each 12 month array.   E.g. an XLOOKUP function in Excel.   Nov-2017 would have a 12 month array...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

     

    Too many measures make me a little confused, so I recreate the measures.

    __R1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Data'[EmployeeID] ),
    FILTER (
        ALL ( Data ),
        EOMONTH('Data'[Date],0)=EOMONTH(MAX('Data'[Date]),0)
    )
)
    __R12 = 
var _Month12=FILTER (
        ALL ( Data ),
            //     EOMONTH ( 'Data'[Date], 0 ) > EOMONTH ( MAX('Data'[Date]), -12 )
            // && EOMONTH ( 'Data'[Date], 0 ) <= EOMONTH ( MAX ( 'Data'[Date] ), 0 )
            EOMONTH ( 'Data'[Date], 0 ) < EOMONTH ( MAX('Data'[Date]), 12 )
            && EOMONTH ( 'Data'[Date], 0 ) >= EOMONTH ( MAX ( 'Data'[Date] ), 0 ))
var _sum12=CALCULATE (
    DISTINCTCOUNT ( 'Data'[EmployeeID] ),
    _Month12
    )
var _max=MAXX(_Month12,[__R1])
return _max

    result:

    Please refer to the attachment below for details. Hope this helps.

     

     

    Best Regards,
    Community Support Team _ Zeon Zheng


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

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

Too many measures make me a little confused, so I recreate the measures.

__R1 = 
CALCULATE (
    DISTINCTCOUNT ( 'Data'[EmployeeID] ),
    FILTER (
        ALL ( Data ),
        EOMONTH('Data'[Date],0)=EOMONTH(MAX('Data'[Date]),0)
    )
)
__R12 = 
var _Month12=FILTER (
        ALL ( Data ),
            //     EOMONTH ( 'Data'[Date], 0 ) > EOMONTH ( MAX('Data'[Date]), -12 )
            // && EOMONTH ( 'Data'[Date], 0 ) <= EOMONTH ( MAX ( 'Data'[Date] ), 0 )
            EOMONTH ( 'Data'[Date], 0 ) < EOMONTH ( MAX('Data'[Date]), 12 )
            && EOMONTH ( 'Data'[Date], 0 ) >= EOMONTH ( MAX ( 'Data'[Date] ), 0 ))
var _sum12=CALCULATE (
    DISTINCTCOUNT ( 'Data'[EmployeeID] ),
    _Month12
    )
var _max=MAXX(_Month12,[__R1])
return _max

result:

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


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