Standardise Date into Single Occurrences

[DataPeter]

Hi All

Long term reader, first time commenter – love this support forum!

Problem: I’m trying to standardise customer records entry dates by occurrence. In short, I want to standardize the first (and subsequent) day of each record irrespective of the specific date/time that has been entered. Table below shows ideal outcome which allow me to group by customers by Day 1, Day2 etc. 

Using PowerBI Desktop and data in on one spreadsheet at the moment. Any support would be most appreciated.

Customer ID	Date/Time Stamp	Outcome
1	1 April 2020  10am	Day 1
1	1 April 2020 1pm	Day 1
1	2 April 2020 10am	Day 2
2	20 May 2020 9am	Day 1
2	20 May 2020 1pm	Day 1
2	21 May 2020 10am	Day 2
3	20 June 2020 10am	Day 1
3	21 June 2020 10am	Day 2

cheers

Peter

[Greg_Deckler]

@DataPeter - Maybe:

Outcome (Column) =
  VAR __Min = MINX(FILTER('Table',[Customer] = EARLIER([Customer])),[Date/Time Stamp])
RETURN
  "Day " & (([Date/Time Stamp] - __Min) * 1. + 1)

[DataPeter]

Thanks @Greg_Deckler 

Thanks for the support, this worked sort of, however it gave me additional decimal places – any thoughts on how to remove these?

[Greg_Deckler · Accepted solution]

@DataPeter - You must have a time component, try:

Outcome (Column) =
  VAR __Min = MINX(FILTER('Table',[Customer] = EARLIER([Customer])),[Date/Time Stamp])
RETURN
  "Day " & FLOOR((([Date/Time Stamp] - __Min) * 1. + 1),1)

[amitchandak]

@DataPeter , all

This should give first date as new column

minx(filter(Table, [Customer ID] =earlier([Customer ID])),[Date/Time Stamp])

You can have a date diff , and get days
Datediff([Date/Time Stamp],minx(filter(Table, [Customer ID] =earlier([Customer ID])),[Date/Time Stamp]),day)

Append Day before

"Day " & Datediff([Date/Time Stamp],minx(filter(Table, [Customer ID] =earlier([Customer ID])),[Date/Time Stamp]),day) 

Same this has been dicssued for cohort report creation in this webinar -https://www.youtube.com/watch?v=Q1vPWmfI25o&t=2420s