December 8, 2020
Being new to the forum, i am kindly asking for help with my problem.
I have a set of data in power pivot and try to create a flattened pivot table from it with 2 date filters.
let's say i have three tables:
T1 [stores] : id, name
T2 [expenses] : id, amount, date_expense
T3 [sales] : id, amount, date_sale
i want to create a pivot table to show the stores with expenses and sales dates in a specific date range (filter), but excel applies by default AND logic and shows only stores with both expenses and sales in that range (what i want is to show the stores that have either an expense or sale in that range : 'OR' logic).
for the sample supplied if we choose the mounth 10/2020 the pivot table is showing no elements although we have sales in the 10th mounth of 2020 (because no expenses in that mounth).
P.S : I am using Office 2016
Thanks in advance.
July 16, 2010
You need to add a date dimension table that contains a list of consecutive dates for the whole year. Then create relationships between the expenses and sales tables to the date table. When you build the PivotTable, make sure you use the date field from date dimension table in your Slicers.