Calculate the median of previous year

[AnthonyXelya]

Hello,

I'm trying to show in a report the median of a filtered year and the median of the previous year of this one.

For the filtered year, it's ok thanks to :

 Median Current Year = MEDIANX(

                               SUMMARIZE(MyTable,

                MyTable[Id_Entity],

                                "Sum", CALCULATE(SUM(MyTable[Value]),

                                       FILTER(MyTable,

                                       MyTable[Date] >= MIN(Calendar[Date])

                                       && MyTable[Date] <= MAX(Calendar[Date]))))

                                      , [Sum]) 

But for the previous year, since I have a filter on the date in another year, my "Table" doesn't return any data... So this doesn't return anything :

Median Last Year =

VAR minDate = DATE(YEAR(MIN(Calendar[Date]))-1, MONTH(MIN(Calendar[Date])), DAY(MIN(Calendar[Date])))

VAR maxDate = DATE(YEAR(MAX(Calendar[Date]))-1, MONTH(MAX(Calendar[Date])), DAY(MAX(Calendar[Date])))

VAR Result = MEDIANX(

                            SUMMARIZE(MyTable,

                                      MyTable[Id_Entity],

                                      "Sum", CALCULATE(SUM(MyTable[Value]),

                                                  FILTER(ALL(Calendar[Date]),

                                                  Calendar[Date] >= minDate

                                                  && Calendar[Date] <= maxDate)) )

                        , [Sum])

RETURN Result 

Any idea to help me to get rid of the year filter in my 2nd formula?

[AnthonyXelya]

Still nobody to help me? 😞

[rajendraongole1]

Hi @AnthonyXelya  Modified 2nd forumale for previous year,  It will correctly calculate the median for the previous year check below measure:

Median Last Year =
VAR minDateCurrentYear = MIN(Calendar[Date])
VAR maxDateCurrentYear = MAX(Calendar[Date])
VAR minDatePreviousYear = DATE(YEAR(minDateCurrentYear) - 1, MONTH(minDateCurrentYear), DAY(minDateCurrentYear))
VAR maxDatePreviousYear = DATE(YEAR(maxDateCurrentYear) - 1, MONTH(maxDateCurrentYear), DAY(maxDateCurrentYear))

VAR Result =
MEDIANX(
SUMMARIZE(
MyTable,
MyTable[Id_Entity],
"Sum", CALCULATE(
SUM(MyTable[Value]),
FILTER(
ALL(Calendar[Date]),
Calendar[Date] >= minDatePreviousYear &&
Calendar[Date] <= maxDatePreviousYear
)
)
),
[Sum]
)
RETURN
Result

Hope it works. pls check

[AnthonyXelya]

Thanks for your answer... sadly it doesn't change anything 😞

[rajendraongole1]

Hi @AnthonyXelya -To ensure the context of other filters is maintained, we can use ALLSELECTED instead of ALL

Median Last Year =
VAR minDate = DATE(YEAR(MIN(Calendar[Date]))-1, MONTH(MIN(Calendar[Date])), DAY(MIN(Calendar[Date])))
VAR maxDate = DATE(YEAR(MAX(Calendar[Date]))-1, MONTH(MAX(Calendar[Date])), DAY(MAX(Calendar[Date])))

VAR Result =
MEDIANX(
SUMMARIZE(
MyTable,
MyTable[Id_Entity],
"Sum",
CALCULATE(
SUM(MyTable[Value]),
FILTER(
ALLSELECTED(Calendar),
Calendar[Date] >= minDate && Calendar[Date] <= maxDate
)
)
),
[Sum]
)
RETURN Result

If this approach still doesn’t work, it might be helpful to inspect the underlying data or test the calculation in a simple scenario to isolate the issue.

[AnthonyXelya]

Sadly, still the same : 

[rajendraongole1 · Accepted solution]

Hi @AnthonyXelya - Can you share the sample data for reference 

I have removed both all and allselected,CALCULATETABLE to create a filtered version of MyTable that only includes rows where the Date column falls within the specified date range of the previous year.

Try this

Median Last Year =
VAR minDate = DATE(YEAR(MIN(Calendar[Date])) - 1, MONTH(MIN(Calendar[Date])), DAY(MIN(Calendar[Date])))
VAR maxDate = DATE(YEAR(MAX(Calendar[Date])) - 1, MONTH(MAX(Calendar[Date])), DAY(MAX(Calendar[Date])))

VAR FilteredTable =
CALCULATETABLE(
MyTable,
Calendar[Date] >= minDate && Calendar[Date] <= maxDate
)

VAR Result =
MEDIANX(
SUMMARIZE(
FilteredTable,
MyTable[Id_Entity],
"Sum", SUM(MyTable[Value])
),
[Sum]
)
RETURN Result

[AnthonyXelya]

It's working! Thank you very much dude!!