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 h...
Mathematical
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. 

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

        If you need the freq measure to have a value of 0 instead of BLANK for the values in question, you should be able to do this:

         

        __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)))
VAR __sum = SUMX(__table2,[__freq])
RETURN
IF(ISBLANK(__sum),0,__sum)