Forum Discussion

hxshxmxhr's avatar
hxshxmxhr
New Member
2 years ago
Solved

Calculate Grouped Data Median for Each Day/Week/Month

Hi, how do I calculate median for grouped data by each day/week/month?   1. I need to calculate the date difference between Date 4 to Date 5 - it should increase by 1 day as the days goes by. ✅✅✅ ...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    2 years ago

    hxshxmxhr 

    pls try this

    1. 

    Column = if(ISBLANK('Table'[Date4]),DATEDIFF(maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&not(ISBLANK('Table'[Date4]))),'Table'[Date4]),'Table'[Actual Date],DAY)+1,DATEDIFF('Table'[Date4],'Table'[Actual Date],DAY)+1)
     
     
    Column 2 = FORMAT('Table'[Actual Date],"mmm")
     
     
    2. create measure
    Measure = MEDIAN('Table'[Column])
    maxdatedif = max('Table'[Column])
    Measure 2 = MEDIANx(VALUES('Table'[ID]),[maxdatedif])
    pls see the attachment below
     
ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
2 years ago

hxshxmxhr 

pls try this

1. 

Column = if(ISBLANK('Table'[Date4]),DATEDIFF(maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&&not(ISBLANK('Table'[Date4]))),'Table'[Date4]),'Table'[Actual Date],DAY)+1,DATEDIFF('Table'[Date4],'Table'[Actual Date],DAY)+1)
 
 
Column 2 = FORMAT('Table'[Actual Date],"mmm")
 
 
2. create measure
Measure = MEDIAN('Table'[Column])
maxdatedif = max('Table'[Column])
Measure 2 = MEDIANx(VALUES('Table'[ID]),[maxdatedif])
pls see the attachment below