Home Forums Power Pivot is a date between two dates – from columns to rows

This topic contains 1 reply, has 2 voices, and was last updated by  DaxOnaBoat 1 week, 1 day ago.

Viewing 2 posts - 1 through 2 (of 2 total)
  • Author
    Posts
  • #11129

    thomas_d
    Participant
    • Started: 1
    • Replies: 0
    • Total: 1

    Hi

    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

    thomas

     

    #11136

    DaxOnaBoat
    Participant
    • Started: 1
    • Replies: 5
    • Total: 6

    Start Date: SDate
    End Date: EDate
    Date In Question: QDate

    IF(ISERROR(DATEDIFF(QDate,Sdate,day))&&ISERROR(DATEDIFF(Edate,Qdate,day)),True,False)

    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.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.