Forum Discussion
Solved
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 ...
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
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