Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Days Between Dates and dealing with Null Dates.

    Posted Aug 10, 2022 06:04 PM
    I work for a college and we have different stages an applicant goes through when they apply to the college. These are Inquiry, Prospect, Application Started, Application Submitted, Application Submitted, Admitted, Confirmed, and Enrolled. I need to calculate the date each application hit this stage and how many days they were on this stage. this worked before when importing a spreadsheet now we have directly connected to the SQL database importing the information.
        Not every applicant goes through the entire process so many have NULL values in various stages. I cannot figure out how to show the datediff if there is a null value so I tried an IF statement with ISBLANK, ="", =0. the system says I can use those on this type of data. I tried converting the table to be if NULL to 0 but it has to be a date. So I added a date that is well before any date the system would use but that can't be hidden like I was hoping.
       Does anyone have a solution that I can use?


    Thank you in advance. :-)

    ------------------------------
    Tim Farnham
    CRM Manager
    ------------------------------


  • 2.  RE: Days Between Dates and dealing with Null Dates.

    Posted Aug 11, 2022 08:09 AM
    Tim, do you have a sample dataset you can share?

    Without that, I here are some things to consider:
     1/ linking a date table linked to your main data set. it's clutch for date/time intelligence processing. (lots of info online for how/why to set one up - link)
     2/ instead of nested IF statements, maybe use either a SWITCH or VAR / RETURN scenario.
     3/ it looks like the dates are in different columns. i recommend unpivoting so all dates are in one column, and the applied, admitted, etc. is a second column, otherwise you'll probably need to incorporate USERELATIONSHIP if your date table will have multiple connections to the RecruitDB

    ------------------------------
    Kellie Delmonico
    Statistical Assistant
    ------------------------------



  • 3.  RE: Days Between Dates and dealing with Null Dates.

    Posted Aug 11, 2022 07:34 PM
    Here is snapshots of what I have and the SQL query I use to import the data.



    select
    [C].FirstName [First Name]
    ,[C].LastName [Last Name]
    ,[C].Datatel_erpid [ERPID]
    ,[C].BirthDate [Birth Date]
    ,[C].elcn_Age [Age]
    ,[CA].PostalCode [Address 1: Zip/Postal Code]
    ,[CT].Datatel_name [Citizenship Status]
    ,[VT].Datatel_name [SC Visa Type]
    ,[SM_O_FunnelStage].[Value] [Funnel Stage]
    ,[AT].Datatel_name [Entry Term]
    ,[AP].Datatel_name [Academic Program]
    ,[ADT].Datatel_name [Admit Type]
    ,[ST].sc_name [Student Type]
    ,[O].CreatedOn [Created On]
    ,[O].elcn_prospectdate [Prospect]
    ,[O].elcn_inquirydate [Inquiry]
    ,[O].elcn_applicationstarteddate [Started]
    ,[O].elcn_applicationsubmitteddate [Submitted]
    ,[O].elcn_applicationcompletedate [Marked Complete]
    ,[O].elcn_admitteddate [Admitted]
    ,[O].elcn_confirmeddate [Confirmed]
    ,[O].elcn_enrolleddate [Enrolled]
    ,[SM_O_StateCode].[Value] [Status]
    from OpportunityBase [O]
    INNER JOIN ContactBase [C] on [O].ParentContactId = [C].ContactId
    LEFT JOIN CustomerAddressBase [CA] on [C].ContactId = [CA].ParentId AND [CA].ObjectTypeCode = 2 AND [CA].AddressNumber = 1
    LEFT JOIN Datatel_citizenshipcodeBase [CT] on [C].datatel_citizenshipstatusid = [CT].Datatel_citizenshipcodeId
    LEFT JOIN Datatel_visatypeBase [VT] on [C].sc_SCVisaType = [VT].Datatel_visatypeId
    LEFT JOIN Datatel_academictermBase [AT] on [O].elcn_anticipatedentrytermid = [AT].Datatel_academictermId
    LEFT JOIN Datatel_academicprogramBase [AP] on [O].elcn_academicprogramofinterestid = [AP].Datatel_academicprogramId
    LEFT JOIN Datatel_studenttypeBase [ADT] on [O].elcn_prospect_studenttypeid = [ADT].Datatel_studenttypeId
    LEFT JOIN sc_studenttypeBase [ST] on [O].sc_StudentType = [ST].sc_studenttypeId
    INNER JOIN StringMapBase [SM_O_FunnelStage] on [O].elcn_FunnelStage = [SM_O_FunnelStage].AttributeValue AND [SM_O_FunnelStage].ObjectTypeCode = 3 and [SM_O_FunnelStage].AttributeName = 'elcn_funnelstage'
    INNER JOIN StringMapBase [SM_O_StateCode] on [O].StateCode = [SM_O_StateCode].AttributeValue AND [SM_O_StateCode].ObjectTypeCode = 3 and [SM_O_StateCode].AttributeName = 'statecode'
    WHERE [O].StatusCode != 344220001 --Do not pull Prospective Student Merged

    ------------------------------
    Tim Farnham
    CRM Manager
    ------------------------------