Forum Discussion
Sum Within Window
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]))
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
1 Reply
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