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 for (Nov-2017 to Nov-2018) and the max value is 318

Feb-2019 would have a 12 month array for (Feb-2019 to Feb-2020) and the max value is 290

 

What I'm counting is employee ID's

 

Result = CALCULATE((DISTINCTCOUNT(Data[EmployeeID])))

Then I'm getting the rolling 12 month count

Measure 1 = 

VAR a =
 MAX ( 'Data'[Date] )

VAR b =
 EDATE ( a, -12 )

RETURN
 CALCULATE (
 [Result],
 FILTER ( ALLSELECTED ( 'Data' ), 'Data'[Date] >b && 'Data'[Date] <= a )
 )

 

Then I was trying to create an array via CALCULATETABLE function

 

Measure 2 = 

 VAR __LAST_DATE = ENDOFMONTH('Data'[Date])
 VAR __DATE_PERIOD = 
 DATESBETWEEN(
 'Data'[Date],
 STARTOFMONTH(DATEADD(__LAST_DATE, -11, MONTH)),
 __LAST_DATE
 )

RETURN
SUMX (
 CALCULATETABLE(
 SUMMARIZE (
 VALUES( Data ),
 Data[Year],
 Data[Month Name],
 Data[Date],
 "SUM",[Measure 1]
 ),

 __DATE_PERIOD

),
 CALCULATE(
 DISTINCTCOUNT('Data'[EmployeeID]),
 ALL('Data'[Date])))

 

 

I've attached my dropbox link but number might be a different to the example above.

 

Any help on this would be greatly appreciated.

https://www.dropbox.com/s/78kq76kfsb1vus7/Test.pbix?dl=0

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.

4 Replies

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community 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.

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try with help from date table 

     

    Rolling 12 = CALCULATE(Max(Table[count]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

     

    or take max of Sum 

     

    Rolling 12 = CALCULATE(MaxX(values(Date[Month]) ,calculate(Sum(Table[count]))),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    I took a look at your file.

    First thing I noticed, you connect the tables Data and DimDate with the fields Data[Date] what has the format Date and DimDate[Dates] what has the format text. For that reason, the connection doesn't work.

    You have to change the connection to Data[Date] and DimDate[Date] which both have the format Date.

     

    Then I think your approach is too complicated. You can solve that with DATESINPERIOD. Try the following measure:

     

    Result New = 
CALCULATE(
    DISTINCTCOUNT( Data[EmployeeID] ),
    DATESINPERIOD(
        'DimDate'[Date],
        MAX( Data[Date] ),
        12,
        MONTH
    )
)

     

     

    Also be aware that you have data from 2017, but your date table starts from 2018 on. For that reason you get blank values. In general, I would make the date table dynamic. Check the following code snippet how you could do that:

    PowerBISnippets/DateTable with date column in fact table.txt at main · selimovd/PowerBISnippets (github.com)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Many thanks for the speedy response.

       

      I've made the necessary changes you've suggested and tried the measure but got this.

       

       

      Rather than just the distinct count, I was hoping to get the [New Rolling 12 month Total" to show its max (12 month array) value per month

       

      https://www.dropbox.com/s/3j55cbcmgy2wt7m/Test2.pbix?dl=0