Finding the date between ranges

[Praj]

Hi Everyone, 

I am new to Power BI and community and I am trying to move excel dashboards to Power BI. Lately, am stuck with the below issue where I need the date when user attended the event between 90-180 days after registration to the company. 

For example, below is my source data. Here Date registered is the date when user registerd for the company, date of events attended are the date when the user attended the event and first attended event is the date when user attended the first event after his registration.

Now, I am trying to identify the dates of event user attended between 0-90 days and 90-180 days after his/her registration like in the below image

I am trying to create a new table from source and upon that I am stuck. Looking for any ppossible approach on how to deal with this problem

[v-yadongf-msft · Accepted solution]

Hi @Praj ,

I want to confimr with you: there are several days which are between 90 and180 days. For example, for name aa, 6/1 and 7/21 are both in 90-180 days. Which day is the result you want? Max or Min ? In my sample file, I get the Min day which are between 90 and 180 days. You can adjust it according  to the result you want. My pbix file is just for your reference.

Please create a new table:

NewTable = 
UNION (
    SELECTCOLUMNS (
        'Table',
        "Name", 'Table'[Name],
        "EventID", 'Table'[Event ID],
        "Date Registered", 'Table'[Date Registered],
        "Event Date", 'Table'[Date of Events attended]
    ),
    SELECTCOLUMNS (
        'Table',
        "Name", 'Table'[Name],
        "EventID", 'Table'[Event ID],
        "Date Registered", 'Table'[Date Registered],
        "Event Date", 'Table'[First attended event by user]
    )
)

You will get a table like this:

Create two measures:

Event attend 0-90 days =
VAR _a =
    SELECTEDVALUE ( 'NewTable'[Date Registered] )
VAR _b =
    CALCULATE (
        MIN ( 'NewTable'[Event Date] ),
        FILTER (
            'NewTable',
            'NewTable'[Name] = SELECTEDVALUE ( 'NewTable'[Name] )
                && 'NewTable'[Event Date] <= _a + 90
        )
    )
VAR _c =
    DATEDIFF ( _a, _b, DAY )
VAR _d =
    IF ( _c = BLANK (), "No", _b )
RETURN
    IF ( ISINSCOPE ( 'NewTable'[Name] ), _d, BLANK () )



Event attend 90-180 days =
VAR _a =
    SELECTEDVALUE ( 'NewTable'[Date Registered] )
VAR _b =
    CALCULATE (
        MIN ( 'NewTable'[Event Date] ),
        FILTER (
            'NewTable',
            'NewTable'[Name] = SELECTEDVALUE ( 'NewTable'[Name] )
                && 'NewTable'[Event Date] > _a + 90
                && 'NewTable'[Event Date] <= _a + 180
        )
    )
VAR _c =
    DATEDIFF ( _a, _b, DAY )
VAR _d =
    IF ( _c = BLANK (), "No", _b )
RETURN
    IF ( ISINSCOPE ( 'NewTable'[Name] ), _d, BLANK () )

You will get the result you want:

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[NikhilChenna]

Hi @Praj ,

I think below solution will help you.

1. Create a new table in Power BI, by using the below dax ,

For creating a table, go to modelling tab and then click on the new table option as show in the above screenshot.

Summarize_table=

SUMMARIZE(

    'table1',

    'table1'[name],

    "MaxDateRegistered",MAX(table1[date registered]),

    "MaxDateEventsAttended",MAX(table1[date of events attended])

)

2. After the table is created, created a calculated column, 

Date diff = MaxDateEventsAttended-MaxDateRegistered

3. Create a similar calculated coulmn for bucket,

Bucket = IF(Date diff>=0 && Date diff<=90,"0-90 days",

                   IF(Date diff>=90 && Date diff<=180,"91 -180 days") )

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[Praj]

Hi @NikhilChenna ,

Thanks much for your quick response. Your solution indeed helped me partially. I got below table however, I was wondering if I can get the specific dates for the events attended between 0-90, 90-180 instead of buckets. Nevertheless, the approach was very helpful.

Cheers,

Praj

[NikhilChenna]

Hi @Praj ,

Are you looking at getting the data based on the individual name and the date of events ID right , 

If that is the case you can do a small change in the summarize table  as below,

Summarize_table=

SUMMARIZE(

    'table1',

    'table1'[name],

    'table1[eventID],

    "MaxDateRegistered",MAX(table1[date registered]),

    "MaxDateEventsAttended",MAX(table1[date of events attended])

)

Check if this works.

Regards,

Nikhil Chenna

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!