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 ...
Need Help
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.