Create Mesuare the get the visit id based on entry and exit date

[Anupam]

HI ,

I have three tables visit data, contain details of customer visit with entry and exit date. A date table (contain all dates) and a customer date with customer date (id and name). I want a table where I can get for each date and each customer the city in which  customer was residing on the given date. 

For date and customer table, I have done cross join, but I am not able to create a measure to get the visit id corresponding to date and customer value. PFA the demo pbix file. 
https://drive.google.com/file/d/11gnlxWPg9hpYtBZr5bPJJd4ZeZWZSkc-/view?usp=sharing

[DataNinja777]

Hi @Anupam,

I downloaded your file and noted that in the file a relationship was created between the transaction ID in the fact table 'VisitData' and the customer ID in the customer dimension table, so I changed that relationship to be between customer ID and customer # in the 'VisitData', as I thought you meant to create that relationship instead. 

The resultant relationship in the data model looks like below:

Then, I created a measures like below to identify the trip locations and ID visited by customers at a given date.

I've then combined these measures in a table measure like below. As you can see, when the customer ID #1 travels from Indore to Mumbai on May 1, 2023, both travel ID #1 and #11 apear on the row of that date with two location names.  

I attach the pbix file below:

Travel location ID.pbix

[ERD]

Hi, a measure might be

VisitID =
VAR selected_date = MAX ( Customer_date[Date] )
VAR customerid = MAX ( Customer_date[id] )
RETURN
    CALCULATE (
        MAX ( VisitData[City] ),
        VisitData[Customer] = customerid,
        VisitData[Entry Date] <= selected_date,
        VisitData[Exit Date] > selected_date
    )

[nirali_arora]

This problem seems similar to the HR Analytics problem which has start date and end date.

You can follow the video and find your solution.

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4 
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI: HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU