How to Filter this Measure?

[morgancampbell]

I have a measure which is as below:

TWER % LWER =

VAR TWER = CALCULATE(sum('Table'[Email Read]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])))

RETURN

VAR LWER = CALCULATE(sum('Table'[Email Read]), FILTER(ALL('Table'),'Table'[Week Rank]=max('Table'[Week Rank])-1))

RETURN

( IF(

        NOT ISBLANK(TWER),

        DIVIDE(TWER - LWER, LWER)

    )

)

This measure displays a percentage of this weeks emails read over last weeks emails read. But I have a slicer on the page that allows me to filter by person, however this measure doesn't filter down as it should. I know that is effected by the filter, after I did some testing, but it seems as if it will only filter nothing or filter everything out.

How would I go about editing this measure so that when I select a name on the slicer, it filters only the data matching that person?

Thanks

[amitchandak · Accepted solution]

@morgancampbell , In this Please, move week, week rank to week table. Create a Separate Week/date table have week Rank there.

That is the best way to make it work.

TWER % LWER =
VAR TWER = CALCULATE(sum('Table'[Email Read]), FILTER(ALL('Week'),'Week'[Week Rank]=max('Week'[Week Rank])))
RETURN
VAR LWER = CALCULATE(sum('Table'[Email Read]), FILTER(ALL('Week'),'Week'[Week Rank]=max('Week'[Week Rank])-1))
RETURN
( IF(
NOT ISBLANK(TWER),
DIVIDE(TWER - LWER, LWER)
)
)

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[morgancampbell]

Spoiler

@amitchandak , How do I go about creating a new a week/date table with only the relevant dates, as opposed to all dates up until the end of the year?