Median number of calls per week for dates selected in slicer

[Banshee]

I have a table with answered calls like:

Date	CallID	Calls
9-24-2025	56546	1
9-25-2025	56582	1
9-25-2025	54656	1

i have a slicer populated from a dates table with WeekEnding.

I want a measure that finds the median number of calls for the range of week endings selected.

Any and all help much appreciated.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on Fabric community.

You need a dimension for dates that has a column WeekEnding for each specific date :

Dates = 
VAR StartDate = DATE(2025,9,20)
VAR EndDate   = DATE(2025,10,05)
RETURN
ADDCOLUMNS (
    CALENDAR ( StartDate, EndDate ),
    "WeekEnding", [Date] + ( 7 - WEEKDAY ( [Date], 1 ) )  
)

Create a relationship between your fact table and the date dimension based on the date column :

Then create the following measure :

Median Calls (Selected Weeks) = 
MEDIANX (
    VALUES ( Dates[WeekEnding] ),
    CALCULATE ( COUNTROWS ( Calls ) )
)

you can find the solution in the attached pbix

[v-kpoloju-msft · Accepted solution]

Hi @Banshee,

Thank you for reaching out to the Microsoft fabric community forum. Also, thanks to @AmiraBedh, for her inputs on this thread. I reproduced the scenario, and it worked on my end. I used my sample data and successfully implemented it.

I am also including .pbix file for your better understanding, please have a look into it.

Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

Thank you for using the Microsoft Fabric Community Forum.

[AmiraBedh · Accepted solution]

Hello !

Thank you for posting on Fabric community.

You need a dimension for dates that has a column WeekEnding for each specific date :

Dates = 
VAR StartDate = DATE(2025,9,20)
VAR EndDate   = DATE(2025,10,05)
RETURN
ADDCOLUMNS (
    CALENDAR ( StartDate, EndDate ),
    "WeekEnding", [Date] + ( 7 - WEEKDAY ( [Date], 1 ) )  
)

Create a relationship between your fact table and the date dimension based on the date column :

Then create the following measure :

Median Calls (Selected Weeks) = 
MEDIANX (
    VALUES ( Dates[WeekEnding] ),
    CALCULATE ( COUNTROWS ( Calls ) )
)

you can find the solution in the attached pbix

[Banshee]

Thank you so much!