Hi all
Four table is my file as follows
Table1 : Sales2016
Columns : StoreID Date Sales16 <this has 400 stores daywise sales
Table2 : Sales2017
Columns : StoreID Date Sales17 <this has 400 stores daywise sales
Table3 : StoreMaster
Columns : StoreID Manager_Name Province <this has 400 stores detail
Table4 : Store_ClosurePeriod
Columns : StoreID Date_From Date_To <this has store closed period in 2016 & 2017, will have multiple data for same store.
How can I sum Sales2016[Sales16] excluding closed period
Eg. if Store_ClosurePeriod table having data of
Store A 3/2/2017 25/2/2017
Store A 7/6/2016 7/7/2016
Store B 3/1/2017 30/1/2017
record1 Store A is closed from 3/12/17 to 25/2/2017 obviously 2017 doesnt have sales but i need to exclude sales of same period in
Sales2016
i tried to calculated column in Sales2016 & Sales2017, it fails when Store_ClosurePeriod table multiple data for same store
any help appreciated.
Four table is my file as follows
Table1 : Sales2016
Columns : StoreID Date Sales16 <this has 400 stores daywise sales
Table2 : Sales2017
Columns : StoreID Date Sales17 <this has 400 stores daywise sales
Table3 : StoreMaster
Columns : StoreID Manager_Name Province <this has 400 stores detail
Table4 : Store_ClosurePeriod
Columns : StoreID Date_From Date_To <this has store closed period in 2016 & 2017, will have multiple data for same store.
How can I sum Sales2016[Sales16] excluding closed period
Eg. if Store_ClosurePeriod table having data of
Store A 3/2/2017 25/2/2017
Store A 7/6/2016 7/7/2016
Store B 3/1/2017 30/1/2017
record1 Store A is closed from 3/12/17 to 25/2/2017 obviously 2017 doesnt have sales but i need to exclude sales of same period in
Sales2016
i tried to calculated column in Sales2016 & Sales2017, it fails when Store_ClosurePeriod table multiple data for same store
any help appreciated.
