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.

selimovd's avatar
selimovd
Icon for Most Valuable Professional rankMost 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