Calculate up to selected date, including average of last 4 weeks

[dange187]

Hi community,

I have a semi complex problem I cant get my head around.

I have a measure which I want to restrict the visible data based on date selection. So if I select todays date then data until today is shown in line chart but if select a date 2 months ago, it only shows up to that date. I have it working for basic measures using the method on SQLBI ( show previous 6 months ) which I've adapted for my custom calendar (based on weeks).

Problem I have is if I try to apply to a measure that includes time based calculations, they all get removed too and breaks the result.

My dax code is: 

Stock Weeks Cover TY =

VAR _maxweek = MAX(dimcal[WeekID])

VAR _minweek = MAX(dimcal[FWYear])

VAR _filtertab = FILTER(SUMMARIZE(dimcalprevious,dimcalprevious[WeekID]),dimcalprevious[WeekID]>=_minweek && dimcalprevious[WeekID]<=_maxweek)

VAR _result =

            CALCULATE(

                [Stock Weeks Cover],

                REMOVEFILTERS(dimcal),

                KEEPFILTERS(_filtertab),

                USERELATIONSHIP(dimcal[WeekID],dimcalprevious[WeekID])

            )

RETURN

    _result

where [Stock Weeks Cover] = DIVIDE([Stock Qty],[Sales Qty P4W AVG])

The [Sales Qty P4W AVG} looks at the last 4 weeks cumulative sales (using dimcal and weekid to navigate the relevant weeks) and divides by 4.

I think this is the part that is breaking. So when I remove filters for dimcal its not working this part out correctly and dividing [Stock Qty] by an incorrect number. 

Any help in keeping the selection range but also keeping the calculation filters would be helpful? Thanks.

[Anonymous · Accepted solution]

Hi @dange187 ,

Based on the information you provided, you can follow these steps:

1.Add new measure.

(1)

Average =

VAR _value =

    CALCULATE (

        AVERAGE ( 'Table'[amount] ),

        FILTER (

            ALL ( 'Table' ),

            [week] <= SELECTEDVALUE ( 'Table'[week] )

                && [week]

                    >= SELECTEDVALUE ( 'Table'[week] ) - 3

        )

    )

RETURN

    IF ( SELECTEDVALUE ( 'Table'[week] ) < 4, BLANK (), _value )

(2)

4WeekAverage =

VAR _1 =

    MAXX ( ALLSELECTED ( 'Table 2' ), 'Table 2'[week] )

RETURN

    IF (

        MAX ( 'Table'[week] ) >= ( _1 - 3 )

            && MAX ( 'Table'[week] ) <= _1,

        [Average],

        BLANK ()

    )

Final output:

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

^{Best Regards,}

^{Ada Wang}

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