cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Standardise Date into Single Occurrences

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

1 ACCEPTED SOLUTION
Super User

@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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
4 REPLIES 4
Super User

@DataPeter - Maybe:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

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?

Super User

@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)``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Super User

@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