Forum Discussion

patrick3's avatar
patrick3
Icon for Helper II rankHelper II
5 years ago

Filter measure by date in table row

Ok, for context, I'm working in a fairly complex PBI Desktop Report - 54 pages, about 40 different data tables - and I'm just bolting something on top. I'd rather built it from scratch, but as it relates to data already formatted in here, I can't.

 

I have a table with email addresses & information about events they attended; Event Name, Type & Date.
1 Person may have attended multiple events.

 

I'm trying to relate that to my Sales Table using the email address as the key.
End goal would be to show Sales & Revenue from people who have attended events, but only after the Event Date.
As they may have purchased something previously, but that's irrelevant in this instance.

 

Steps taken so far:

  • Created a new bridge table with the unique email addresses of event attendees
  • Related that to both the original Event Attendee Data & the Sales Data, using Filter Direction 'both'.

This works - by select individual events on a table, I can now show revenue related to event attendees.

But, how do I limit that only show revenue that came in after the event happened?

I have a close date field on the Sales Data.

 

Thanks,

Patrick 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi patrick3,

    It sounds like a common multiple date fields range analysis requirement, if that is the case, you can take a look at the following link about the analysis date range of multiple date fields.

    Spread revenue across period based on start and end date, slice and dase this using different dates 

    Before You Post, Read This#'start date', 'end date' 
    If the above not help, please share some dummy data with raw table schema and expected results to test.

    How to Get Your Question Answered Quickly

    Regards,
    Xiaoxin Sheng

    • patrick3's avatar
      patrick3
      Icon for Helper II rankHelper II
      5 years ago

      Hi Anonymous ,

      Thanks for your reply - had a look at the suggested posts and they should help, but I still can't translate the concepts 100% to my use case.

      Here's a mock-up .PBIX file that will help you understand what model I'm working with
      https://we.tl/t-gccGYePSNG

      The goal here would be create a measure called "Event Revenue", which only shows the revenue that came in after someone attended a event (bonus points if we can limit that timeframe to say, 3 weeks).

      So in this example:

       

      The "Total Revenue" measure is showing £2,000 revenue for the event on the 12th of January. 
      But as you can see in the 3rd table, only £1,000 of that revenue came in after the event happened, the other £1,000 happened before the event, so is irrelevant for my purposes. So I'd like the "Event Revenue" measure to show £1,000, in this case.

      Thanks,
      Patrick

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi patrick3,

        I can't get data from the third part web drive due to policy reasons, can you please upload it to onedrive for business?

        Regards,

        Xiaoxin Sheng