Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

Mann-Kendall Test

Anonymous reached out to me in a private message and asked me about something called the Mann-Kendall Test. Apparently it is a statistics thing created by evil mathematicians. You can read about it here:

https://www.real-statistics.com/time-series-analysis/time-series-miscellaneous/mann-kendall-test/

 

This PBIX file implements the Mann-Kendall Test completely in measures, making it 100% dynamic. The data used in the PBIX file is the same as in the URL from real-statistics. 

 

The most interesting measure is the one for calculating "S". Calculating "S" looks like it would involve iteration, and it basically does. As we all know, normally iteration is out-of-bounds for DAX but luckily, with some tricky nested DAX we can overcome it!

 

 

__S = 
VAR __table1 = ADDCOLUMNS('MK',"__S",
    VAR __value = [Value]
    VAR __table = FILTER(ALLSELECTED('MK'),[ID] < EARLIER([ID]))
    VAR __pos = COUNTX(FILTER(__table,[Value]<__value),[ID])
    VAR __neg = COUNTX(FILTER(__table,[Value]>__value),[ID])
    RETURN
        __pos - __neg)
RETURN
SUMX(__table1,[__S])

Enjoy!

 

Mathematical

15 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Greg

     

    Firstly, wow and i am lost for words. The reason i reached out to you, is because you are the guy/person we reach out to when all hope is lost in the word or Power BI.

     

    thank you and god bless.

    A master at work.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Greg

    I managed to copy use your excellent work to work out the __S for my data and the measure is.

    __s = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
RETURN
SUMX(ComplaintsByFiscalMo, 
    SUMX(ComplaintsByFiscalMo,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]), 1,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]), -1))
    )
)+0

    I cannot for the love of god get the __Freq using my data. 

    i have a sample file here: https://www.dropbox.com/s/bpvte6ms66wv8u5/apples.pbix?dl=0

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Hmm Anonymous, try this for frequency:

       

      __freq = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
VAR __table = GROUPBY(ComplaintsByFiscalMo,[CountComplaints],"__ties",COUNTX(CURRENTGROUP(),[FISCAL_MON_START_DT]))
VAR __table1 = ADDCOLUMNS(__table,"__ties1",[__ties] - 1)
VAR __table2 = ADDCOLUMNS(__table1,"__freq",IF([__ties1]=0,0,[__ties1]*([__ties1]+1)*(2*[__ties1]+7)))
RETURN
SUMX(__table2,[__freq])

      I should probably break my bad habit of using SUMMARIZE.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Greg, aka DAX Guru.

        the dax is spot on an gives the correct end result.

        The only problem i can see with the whole Mann Kandell calculation for __s and __freq is when you have blank data points. 

        In R you have to input 12 data point for 12 months and months with no data are shown as 0 not blank. So how do you get around this in DAX or how can i amend my formula to add 0 to the table below.

        R Input top part of screen and results bottom part.


         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        Power BI Issues to over come

         

         

         

         

         

         

         

         

         

         

         

         

         

         

         

        The Mann kandell falls apart when you have less then 12 data points. if we have 12 data points all the values are 100% spot on with R. 

  • deschman's avatar
    deschman
    Regular Visitor
    5 years ago

    Dear Greg_Deckler,

     

    I absolutely love your solution and it performs very well. I would like to ask you a question regarding the slope. Mann-Kendall tells us that there is a tren or there is none. for the direction o the trend I read it is advised to use Sen's slope. I tried to figure out if this is the slope that is calculated in your solution. Can you elaborate on this a bit?

     

    Thanks in advance.

  • gdonaldson's avatar
    gdonaldson
    New Member
    4 years ago

    Hello,

    I seem to be having some issues downloading the .pbix file. Wondering if you could try and repost? Not sure if it's an issue on my end. 

    Thanks,

    Graeme