Median of the Total from Each Month

[TJK]

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

    )

)

[Poojara_D12]

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])

[TJK]

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

[Greg_Deckler]

@TJK So something like:

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

[TJK]

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]

  )

[v-xuxinyi-msft]

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.

[TJK]

Hello @v-xuxinyi-msft 

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

[v-xuxinyi-msft]

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

[TJK]

Hi @v-xuxinyi-msft 
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...