cancel
Showing results 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.

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
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
)
)``````
4 REPLIES 4
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
)
)``````
Community Support

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.

Employee

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.

Super User

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.

Announcements

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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors