CODEWITHSUNDEEP
powerbidax
Shadowsong
Shadowsong

Reputation: 1

How do I create a measure based on the six weeks following the filtered date in Power BI?

I'm trying to create a sales projection measure that will sum up the six weeks of forecast data AFTER the date specified in the filter. However, the two methods I've tried so far give me blank results, presumably because I'm asking for records that match the initial date filter AND which match the mutually incompatibly six-following-weeks filter.

Here's the current 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)], datesbetween('Date'[Fiscal Week],mindate,maxdate))

I've also tried using DATEADD('Date'[Fiscal Week],7,DAY), DATEADD('Date'[Fiscal Week],14,DAY) etc on through 42, instead of datesbetween. Both options gave me blank results.

Maybe I need to create a pair of date measures, next6wkstart and next6wkend? But then I'm not sure how to reference them. If this were SQL I'd just join the Date table to itself WHERE date2.FiscalWeek >= date.next6wkstart AND date2.FiscalWeek <= date.next6wkend, but I don't know how to do anything like that in DAX.

Upvotes: 0

Views: 108

Answers (1)

Vilmar
Vilmar

Reputation: 1

See if it helps PBIX here

Sell Thru Projection = 
VAR mindate = MIN ( dCalendarAux[DateAux] ) + 1
VAR maxdate = MAX ( dCalendarAux[DateAux] ) + 2
RETURN
CALCULATE (
    [Forecast],
    KEEPFILTERS (
        FILTER (
            ALL ( dCalendar[Date] ),
            dCalendar[Date] >= mindate
                && dCalendar[Date] <= maxdate
        )
    )
)

enter image description here

Upvotes: 0

Related Questions