Sum Within Window

[RSD]

Hi There,

Could you please help me with below issue in power bi?

As Shown in Below Image, I need help help in ("Shipment Within event window") Column

In Current scenario , We are Forming Event window as an when we find nonnblank value for 'Forecat Unit' .(Tag=1).We are Creating Event window for 2 Consecutive weeks and taking sum of Shipment QTY. E,g For Forecast Unit = 700 then Shipment Within event window =229+222+79=530.

BUt I need to get rid of Overlapping window issue. e.g For Forecast Unit = 250 then Shipment Within event window=171,I need to ignore 71 because it has been covered in above window.

Formula:

Shipment within event window =

             CALCULATE(SUM('Forecast Code'[Shipment QTY]),DATESBETWEEN('Forecast Code'[Master Date],FIRSTDATE('Forecast Code'[Master Date]),

            FIRSTDATE('Forecast Code'[Master Date])+14),ALLEXCEPT('Forecast Code','Forecast Code'[Customer],'Forecast Code'[Master Date],'Forecast Code'[BC]))

[v-lili6-msft · Accepted solution]

hi  @RSD 

For your case, try this logic:

Step1:

Add a rank column for Master Date for each BC&Customer.

rank = RANKX(FILTER('Forecast Code','Forecast Code'[BC]=EARLIER('Forecast Code'[BC])&&'Forecast Code'[Customer]=EARLIER('Forecast Code'[Customer])),[Master Date],,ASC)

Step2:

then use this logic to get the column

Result = 
var _rankfortag1=IF('Forecast Code'[Tag]=1,RANKX(FILTER('Forecast Code','Forecast Code'[BC]=EARLIER('Forecast Code'[BC])&&'Forecast Code'[Customer]=EARLIER('Forecast Code'[Customer])&&'Forecast Code'[Tag]=1),[Master Date],,ASC))
var _rank=IF('Forecast Code'[Tag]=1,RANKX(FILTER('Forecast Code','Forecast Code'[BC]=EARLIER('Forecast Code'[BC])&&'Forecast Code'[Customer]=EARLIER('Forecast Code'[Customer])),[Master Date],,ASC)) 
var _nextrank=CALCULATE(MIN('Forecast Code'[rank]),FILTER(ALLEXCEPT('Forecast Code','Forecast Code'[BC],'Forecast Code'[Customer]),'Forecast Code'[rank]>EARLIER('Forecast Code'[rank])&&'Forecast Code'[Tag]=1)) return
IF(_rankfortag1=1,CALCULATE(SUM('Forecast Code'[Shipment QTY]),FILTER(ALLEXCEPT('Forecast Code','Forecast Code'[BC],'Forecast Code'[Customer]),'Forecast Code'[rank]>=_rank&&'Forecast Code'[rank]<=_nextrank)),IF(_rankfortag1>1,CALCULATE(SUM('Forecast Code'[Shipment QTY]),FILTER(ALLEXCEPT('Forecast Code','Forecast Code'[BC],'Forecast Code'[Customer]),'Forecast Code'[rank]>_rank&&'Forecast Code'[rank]<=_nextrank)
)))

Result:

here is sample pbix file, please try it.

Regards,

Lin