November 6, 2018 at 5:44 pm #11129
- Started: 1
- Replies: 0
- Total: 1
I have two tables uploaded in Power Pivot.
table1 contains a column with a list of items (A, B, C…) and two columns with the start date and end date of some events affecting these items. For a same item, mulitple events can overlap so the same item can be in multiple rows.
table2 contains one column with the items (A, B, C…) and a second column with a timestamp, and a third column with a metric associated to the item in column1 for the timestamp in column2 (sales for example). concatenation of column1 and column2 is a unique id for this table (only one row per item and timestamp).
I want to add a calculated column in table2 that would be a Y/N flag representing whether or not the item (column1) had an event for the timestamp in column2 (basically, is the timestamp in column 2 comprised between a start date and an end date described in the table 1)
honestly i don’t know how to crack this one, i can’t even think of a scrappy solution, your help would be immensely appreciated!
thanks a lot
thomasNovember 7, 2018 at 8:58 pm #11136
- Started: 1
- Replies: 5
- Total: 6
Start Date: SDate
End Date: EDate
Date In Question: QDate
This says if Qdate > Sdate (Iserror returns true) and Sdate < Edate (Iserror returns true), then true, otherwise false.
Extra tip, don’t use Y/N flags, use true/false binaries, it performs faster than using a Y/N string output.
You must be logged in to reply to this topic.