How To calculate Frequency of continuous dates based on ID in Power BI

[mydatastories]

Hi everyone,

I was just wondering if anyone can advise the best/efficient way to calculate frequency based on continuous dates and record id.

Scenario

• I want to be able to calculate these customers that are Frequent and Non-Frequent (status)based on the time their stay at the hotel and this must be only on the weekday (Monday -Friday)
• I have [CheckinDate] and [CheckOutDate] which give days (but this is required to calculate only Monday to Friday Stays, so we’re excluding weekend )
• [Customer type] is the status when a customer has meet which we’re trying to calculate and this will be our alias column
• I have [Customer ID] which is the same every time when a customer stays in a hotel, so one [Customer ID] can have many stays but I am interested in these based on below business logic.

Business Logic

1. 10 days (Monday -Friday) or a 4 period/frequency of staying in a hotel in 6-months period based on check-in date
2. 20 days (Monday – Friday) or a 7 period/frequency of staying in a hotel in a 12-month period based on check-in date

SELECT  [Customer ID]
      ,[CheckinDate]
      ,[CheckOutDate]
      ,[TotalDaysInHotel]
      ,[Customer type ]---This is alias table and is what we're trying to calculate(Frequency)
  FROM [Dev].[dbo].[Customer]
  WHERE [Customer ID] = '1'

Anything else with earlier, [CheckOutDate] than, [CheckinDate] is Data Quality issue as is not logically possible

I have attached the final results with Alias column [Customer type ] which is a result of above business logic

Many thanks for your help in advance.

[v-shex-msft]

HI @mydatastories,

If you can please share some sample data/pbix file (it should has same structure as original file) and expected result to help us clarify your requirement and test to coding formula.

Regards,

Xiaoxin Sheng

[mydatastories]

Hi everyone,

I was just wondering if anyone can advise the best/efficient way to calculate frequency based on continuous dates and record id.

Scenario

• I want to be able to calculate these customers that are Frequent and Non-Frequent (status)based on the time their stay at the hotel and this must be only on a weekday (Monday -Friday)
• I have [CheckinDate] and [CheckOutDate] which give days (but this is required to calculate only Monday to Friday Stays, so we’re excluding weekend )
• [Customer type] is the status when a customer has meet which we’re trying to calculate and this will be our alias column
• I have [Customer ID] which is the same every time when a customer stays in a hotel, so one [Customer ID] can have many stays but I am interested in these based on below business logic.

Business Logic

1. 10 days (Monday -Friday) or a 4 period/frequency of staying in a hotel in 6-months period based on check in date
2. 20 days (Monday – Friday) or a 7 period/frequency of staying in a hotel in 12-months period based on check in date

SELECT  [Customer ID]

      ,[CheckinDate]

      ,[CheckOutDate]

      ,[TotalDaysInHotel]

  FROM [Dev].[dbo].[Customer]

Anything else with earlier ,[CheckOutDate]than ,[CheckinDate] is Data Quality issue as is not logically possible

I have attached the final results with Alias column [Customer type ] which is a result of above business logic

Many thanks for your help in advance.