How to use two different date filters in a measure

[shadowsong]

I want to create a measure that takes the current week's inventory and divides by the average of predicted sell through over the next six weeks. Right now I'm trying to get the formula for average future sell through to work. I have a page filter that specifies the current fiscal week, and then this formula for the measure:

Sell Thru Projection =

VAR mindate= min('Date'[Fiscal Week]) +1

VAR maxdate = min('Date'[Fiscal Week]) +42

RETURN

CALCULATE(

    [CDP Forecast Sell Thru (Units)]/6,

    FILTER (

        ALL ( 'Date'[Fiscal Week] ),

        'Date'[Fiscal Week] >= mindate

            && 'Date'[Fiscal Week] <= maxdate

    )

)

However, it returns blank results. What am I doing wrong?

[shadowsong · Accepted solution]

The trick was to change the ALL() reference to the whole table instead of a single column.

Sell Thru Projection = 
VAR mindate = min('Date'[Fiscal Week]) +1
VAR maxdate = min('Date'[Fiscal Week]) +42
RETURN 
CALCULATE( 
    [CDP Forecast Sell Thru (Units)]/6, 
    FILTER (
        ALL('Date'),
        'Date'[Fiscal Week] >= mindate && 'Date'[Fiscal Week] <= maxdate
    )
)

[Anonymous]

Hi @shadowsong ,

Your approach to calculating the average future sell-through is on the right track, but there’s a small issue in your formula.

The problem lies in the filter condition. You’re using ALL('Date'[Fiscal Week]), which removes all filters on the ‘Date’ table. This means it’s not considering the page filter for the current fiscal week.

You can use ALLSELECTED('Date'[Fiscal Week]). This will retain the page filter for the current fiscal week while allowing the filter on the ‘Date’ table to be applied.

This is the corrected DAX code: 

Sell Thru Projection =
VAR mindate =
    MIN ( 'Date'[Fiscal Week] ) + 1
VAR maxdate =
    MIN ( 'Date'[Fiscal Week] ) + 42
RETURN
    CALCULATE (
        [CDP Forecast Sell Thru (Units)] / 6,
        FILTER (
            ALLSELECTED ( 'Date'[Fiscal Week] ),
            'Date'[Fiscal Week] >= mindate
                && 'Date'[Fiscal Week] <= maxdate
        )
    )

Best Regards

Yilong Zhou

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

[shadowsong]

Sadly, this gives me exactly the same blank results.  My understanding is that retaining the date filter means that I am looking for records that match both "this week" and "six future weeks" criteria, which is of course not possible, so blank results are expected.

[amitchandak]

@shadowsong ,

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

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

These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))

Next 6 weeks = CALCULATE(sum('Table'[sell]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])+1 && 'Date'[Week Rank]<=max('Date'[Week Rank]) +6 ))

You can now divide the two.

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.