Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
shadowsong
Employee
Employee

How to use two different date filters in a measure

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?
1 ACCEPTED SOLUTION
shadowsong
Employee
Employee

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
    )
)

View solution in original post

4 REPLIES 4
shadowsong
Employee
Employee

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
    )
)
v-yilong-msft
Community Support
Community Support

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.

 

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
Super User
Super User

@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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.