Divide current months value by average of monthy totals YTD, still enabling a slicer

[dlopesc04]

I'm at my wits end lol. I have to divide totals of values from the current month by the average of the totals of the months to date.
Example:

Months	Total of value by month
Current month (April)	10
March	5
February	0
January	10

So, average of totals is: (5+10)/2 = 7.5
Then I have to get: [Total of current month]/[average of totals] = 10/7.5 = 1.333

So far so good, but there must be a date slicer on the page, through wich the user will be able to select which month to analyze and the division will be of the YTD, so If I slice "March", the average of totals will be of February and January. And if I selected a month from a different year, the measure has to get the YTD corresponding to that year.

Problem is, since the slicer is always filtering just a single month on the page, the average would always return just the value of the current month, no matter if I used ALLEXCEPT, ALL or REMOVEFILTERS, it wouldnt aggregate the year and do an average of the sum by month (I was using a SUMMARIZE by month, then average, something like below).

Intended Average Volume =

VAR date2 = MAX(d_calendar[Data])

VAR year = YEAR(date2)

VAR table1 = CALCULATETABLE(SUMMARIZE(f_fact,f_fact[month],"volume",SUM(f_fact[volume])),f_fact[year]=year)

RETURN

AVERAGEX(table1,[volume])

I ended up creating a calculated table, disconnected from my date table, using the following measure the get the average in a way that would ignore the slicer:

Average Volume =

VAR date = MAX(d_calendar[Date])

VAR year = YEAR(date)

VAR month = MONTH(date)

VAR calc =

    CALCULATE(

        AVERAGE(clients[volume]), clients[year] = year

    )

RETURN

calc

This works, BUT not with RLS, sigh. I'm sure I'm missing something very simple but I'm so deep I can't see straight anymore. How do I divide current months value by the average of monthy totals YTD, still enabling a slicer to work as described?

[v-tianyich-msft]

Hi @dlopesc04 ,

First of all, you definitely need to use an unrelated date table to do the calculations you want, but to implement RLS, you probably need TREATAS() to pass the filter. Here's a tutorial for you:DAX limitations with inactive relationships and row-level security (RLS) - SQLBI

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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