Forum Discussion
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. ✅✅✅
2. I need to calculate the median for these 2 IDs. Example for median by days:
Actual Date = 21/2/2024, I want the median to be =Median(1, 20) which equals to 10.5.
Actual Date = 22/2/2024, the median should be =Median(2, 21) which is 11.5.
3. Let's do median by month next, which is February 2024:
=Median(8, 21) hence 14.5 ➡️ it considers the very last datediff for that month for each ID
How do I calculate this in Power BI or DAX?
Thank you
pls try this
1.
Column = if(ISBLANK('Table'[Date4]),DATEDIFF(maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&¬(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 measureMeasure = MEDIAN('Table'[Column])maxdatedif = max('Table'[Column])Measure 2 = MEDIANx(VALUES('Table'[ID]),[maxdatedif])pls see the attachment below
3 Replies
ryan_mayuSuper User
pls try this
1.
Column = if(ISBLANK('Table'[Date4]),DATEDIFF(maxx(FILTER('Table','Table'[ID]=EARLIER('Table'[ID])&¬(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 measureMeasure = MEDIAN('Table'[Column])maxdatedif = max('Table'[Column])Measure 2 = MEDIANx(VALUES('Table'[ID]),[maxdatedif])pls see the attachment below- ryan_mayu
you are welcome
