Forum Discussion

DataPeter's avatar
DataPeter
Frequent Visitor
6 years ago
Solved

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 fir...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    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)

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

DataPeter - Maybe:

Outcome (Column) =
  VAR __Min = MINX(FILTER('Table',[Customer] = EARLIER([Customer])),[Date/Time Stamp])
RETURN
  "Day " & (([Date/Time Stamp] - __Min) * 1. + 1)
DataPeter's avatar
DataPeter
Frequent Visitor
6 years ago

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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)