topic Re: Median of the Total from Each Month in DAX Commands and Tips

Median of the Total from Each Month

TJK — Wed, 29 Jan 2025 15:52:56 GMT

The current output of my measure is the total count across all months. I want to adjust my measure so that the output is the Median of the total for each month.

Current Output:

Output = 229

Desired Output:

July: 56

August: 56
September: 60
October: 57

Median = 56.5

My measure is exluding November and December, so sometimes the median will be across 4 months, sometimes 5 months, and sometimes 6 months, depending on the time of year.

Measure 3 =

VAR SelectedDate = SELECTEDVALUE('LOI Date'[Days Until EOM Sort])

VAR CurrentTime = TIME(HOUR(UTCNOW()) - 5, MINUTE(UTCNOW()), SECOND(UTCNOW()))

VAR ThresholdTime = TIME(12, 30, 0)

VAR TodayDate = TODAY()

VAR StartDate = EDATE(TodayDate, -7) -- 7 months back from today

RETURN

CALCULATE(

DISTINCTCOUNT('Opportunity'[Id]),

FILTER(

'Opportunity',

IF(

CurrentTime > ThresholdTime,

'Opportunity'[Days Until EOM] < SelectedDate,

'Opportunity'[Days Until EOM] <= SelectedDate

) &&

MONTH('Opportunity'[LOI Date]) = MONTH('Opportunity'[Close Date]) &&

YEAR('Opportunity'[LOI Date]) = YEAR('Opportunity'[Close Date]) &&

'Opportunity'[LOI Date] >= StartDate &&

'Opportunity'[LOI Date] <= TodayDate &&

NOT (MONTH('Opportunity'[LOI Date]) IN {11, 12}) -- Exclude Nov and Dec

)

Re: Median of the Total from Each Month

Greg_Deckler — Wed, 29 Jan 2025 16:11:45 GMT

TJK So something like:

Measure 4 = MEDIANX( ADDCOLUMNS( DISTINCT( 'Dates'[Month] ), "__Value", [Measure 3] ), [__Value] )

Re: Median of the Total from Each Month

TJK — Wed, 29 Jan 2025 16:25:44 GMT

Hi Greg_Deckler the measure you provided is giving me the total across all months (same output as my original measure 3)

Measure 4 =

MEDIANX(

ADDCOLUMNS( DISTINCT( 'LOI Date'[MonthNum] ), "__Value", [Measure 3] ),

[__Value]

)

Re: Median of the Total from Each Month

Anonymous — Thu, 30 Jan 2025 09:52:03 GMT

Hi TJK

Thanks for the reply from Greg_Deckler .

TJK , the following test is for your reference.

Sample:

Measure:

Median = VAR _table = SUMMARIZE ( 'Table', 'Table'[Month], "Monthly", SUM ( 'Table'[Value] ) ) RETURN MEDIANX ( _table, [Monthly] )

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Median of the Total from Each Month

TJK — Thu, 30 Jan 2025 13:29:24 GMT

Hello Anonymous

I do not have a table with the values for each month. I want to perform the median using my "Measure 3"

Re: Median of the Total from Each Month

Anonymous — Fri, 31 Jan 2025 08:46:29 GMT

Hi TJK

It seems that two tables are used in your Measure3, could you please provide some sample data about these two tables so that we can help you better. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Please remove any sensitive data in advance.

Best Regards,
Yulia Xu

Re: Median of the Total from Each Month

TJK — Fri, 31 Jan 2025 17:39:35 GMT

Hi Anonymous
Here is a link to the sample data. Thank you for your help.
https://www.dropbox.com/scl/fi/m0qwug2lz7w4m6a8n8duf/Count-of-Records-After-Selected-Date.pbix?rlkey=ng8t39vc5l96epwp0980kf6sx&st=yy08ip16&dl=0

Re: Median of the Total from Each Month

Poojara_D12 — Sat, 01 Feb 2025 04:41:07 GMT

Hi TJK

Can you try this dax:

Median Monthly Count = VAR StartDate = EDATE(TODAY(), -7) -- 7 months back from today VAR TodayDate = TODAY() VAR CurrentTime = TIME(HOUR(UTCNOW()) - 5, MINUTE(UTCNOW()), SECOND(UTCNOW())) VAR ThresholdTime = TIME(12, 30, 0) -- Create a table with distinct count per month VAR MonthlyCounts = ADDCOLUMNS( SUMMARIZE( 'Opportunity', 'Opportunity'[LOI Date].[Year], 'Opportunity'[LOI Date].[Month] ), "@Count", CALCULATE( DISTINCTCOUNT('Opportunity'[Id]), 'Opportunity'[LOI Date] >= StartDate && 'Opportunity'[LOI Date] <= TodayDate && NOT (MONTH('Opportunity'[LOI Date]) IN {11, 12}), -- Exclude Nov and Dec IF( CurrentTime > ThresholdTime, 'Opportunity'[Days Until EOM] < SELECTEDVALUE('LOI Date'[Days Until EOM Sort]), 'Opportunity'[Days Until EOM] <= SELECTEDVALUE('LOI Date'[Days Until EOM Sort]) ), MONTH('Opportunity'[LOI Date]) = MONTH('Opportunity'[Close Date]), YEAR('Opportunity'[LOI Date]) = YEAR('Opportunity'[Close Date]) ) ) -- Compute the median from the monthly totals RETURN MEDIANX(MonthlyCounts, [@Count])

Re: Median of the Total from Each Month

TJK — Thu, 06 Feb 2025 22:41:02 GMT

Hi Poojara_D12

Your measure did not give me the expected outcome. Here's a sample pbix file that describes what I expect the outcome to be.

https://www.dropbox.com/scl/fi/m0qwug2lz7w4m6a8n8duf/Count-of-Records-After-Selected-Date.pbix?rlkey=ng8t39vc5l96epwp0980kf6sx&e=1&st=yy08ip16&dl=0