Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
DataPeter
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 IDDate/Time StampOutcome
11 April 2020  10amDay 1
11 April 2020 1pmDay 1
12 April 2020 10amDay 2
220 May 2020 9amDay 1
220 May 2020 1pmDay 1
221 May 2020 10amDay 2
320 June 2020 10amDay 1
321 June 2020 10amDay 2

 

cheers

Peter

1 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)

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
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)

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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?

 

DataPeter_0-1599452916627.png

 

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

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
amitchandak
Super User
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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.