Measure evaluates for all dates only when current week is selected on a slicer

[Anonymous]

Hi,

I have a graph that has a column for a variables daily value and a line for the weekly value. The graph plots 6 weeks of data up until the selected week in a slicer (using a calulation group below). Daily data is being added to the data set. When a historical week is selected, this works perfectly as shown here:

For some reason, when the current week is selected, the weekly average calculates for all dates to the beginning of the date table. As shown here:

My weekly average formula is here:

weekly average =
VAR currentw =
    MAX ( date_table[WeekOffset] )
VAR enddate =
    CALCULATE ( MAX ( 'Previous Dates'[Date] ), ALLSELECTED ( 'Previous Dates' ) )
VAR startdate =
    CALCULATE ( MIN ( 'Previous Dates'[Date] ), ALLSELECTED ( 'Previous Dates' ) )
VAR period =
    DATESBETWEEN ( date_table[Date], startdate, enddate )
VAR result =
    CALCULATE (
        [Measure],
        FILTER ( ALL ( date_table ), date_table[WeekOffset] = currentw ),
        period
    )
RETURN
    result

I've used a secondary date table ('previous dates') with a calulation group to show the 6 weeks up until the selected week. The code is below. (This was based on SQL BI's tutorial here: https://www.youtube.com/watch?v=d8Rm7dwM6gc)

VAR NumDays = -42
VAR endw =
    MAX ( date_table[Date] )
VAR previousdates =
    DATESINPERIOD ( 'Previous Dates'[Date], endw, NumDays, DAY )
VAR result =
    CALCULATE (
        selectedmeasure(),
        REMOVEFILTERS ( date_table ),
        KEEPFILTERS ( previousdates ),
        USERELATIONSHIP ( date_table[Date], 'Previous Dates'[Date] )
    )
RETURN
    result

To help debug the issue, I created a table and printed the first date and last date of the period variable as well as the result here.
Code: 

FIRSTDATE(period)&"/-/"&LASTDATE(period)&"/-/"&result

It appears that the period has the correct dates in (6 weeks to the selected week), however it has evaluated the measure for dates over a year ago. This only happens when selecting the current week.

Does anyone know why this is happening please?

Thanks in advance,

Charlie

[Anonymous]

@amitchandak - thank you but this is not the bit I have a problem with. My approach is similar to your ranking (I call it offset). However, the bit I am trying to understand is why this evaluates for the whole date table when I have the current week selected (and there is not much data in the current week). This works well when I select any previous weeks.

Thanks

[Anonymous]

Perhaps a clearer way of showing the problem.

I have one measure:

Last 6 weeks Weekly Average = 
VAR NumDays = -42
VAR endw =
    MAX ( date_table[Date] )
VAR previousdates =
    DATESINPERIOD ( 'Previous Dates'[Date], endw, NumDays, DAY )
VAR result =
    CALCULATE (
        [MPS (weekly average)],
        REMOVEFILTERS ( date_table ),
        KEEPFILTERS ( previousdates ),
        USERELATIONSHIP ( date_table[Date], 'Previous Dates'[Date] )
    )
RETURN
    result

This calls the measure:

MPS (weekly average) =
VAR currentw =
    MAX ( date_table[WeekOffset] )
VAR enddate =
    CALCULATE ( MAX ( 'Previous Dates'[Date] ), ALLSELECTED ( 'Previous Dates' ) )
VAR startdate =
    CALCULATE ( MIN ( 'Previous Dates'[Date] ), ALLSELECTED ( 'Previous Dates' ) )
VAR period =
    DATESBETWEEN ( date_table[Date], startdate, enddate )
VAR result =
    CALCULATE (
        [M_MPS (Chapter)],
        FILTER ( ALL ( date_table ), date_table[WeekOffset] = currentw ),
        period
    )
RETURN
    result

This works to show me the weekly average of the 6 weeks up until the date selected on the slicer for all slicer selections, except when selecting the current week.

[amitchandak]

@Anonymous , I usually use week rank column

Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

you can get 6 week

Last 6 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

Avg Last 6 weeks = CALCULATE(Averagex(Values('Date'[Week Rank]), [orders ]) , FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-6 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8