Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Anonymous
      Not applicable
      3 years ago

      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.