Duplicate Dates was Specified in the call to function "DATEADD" Error

[Chalk442]

Hello Power BI Community,

I am receiving an error when trying to calculate the percentage difference between values in a given time period.

Data:

1) numberOfCatsbyBreed = The number of cats there are for each breed.
2) bornTime = Multiple dates of when the cats were born, some of the same value (born on the same day), but with different timestamps.

numberOfCatsbyBreed	bornTime
364	04/04/2020 12:15PM
323	04/04/2020 1:30PM
345	05/05/2020 5:30PM
123	06/06/2020 3:30PM

Measure:
%CHANGE =
VAR  CurrentValue = [Count of numberOfCatsbyBreed]
VAR PreviousValue =
SWITCH(

              TRUE(),
              ISINSCOPE('Cats'[bornTime].[Day]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Cats'[bornTime], -1, DAY)),

              ISINSCOPE('Cats'[bornTime].[MONTH]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Cats'[bornTime], -1, MONTH)),
              ISINSCOPE('Cats'[bornTime].[QUARTER]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Cats'[bornTime], -1, QUARTER)),
              ISINSCOPE('Cats'[bornTime].[YEAR]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Cats'[bornTime], -1, YEAR))

)

RETURN

DIVIDE(

            CurrentValue - PreviousValue,

            PreviousValue

)

Visualization (Area Chart):

Shared axis - bornTime

Column values - %CHANGE

Line values - Count of numberOfCatsbyBreed

Ultimately, I am looking for this (but with my data):

https://www.youtube.com/watch?v=L2ELtem2iGY

Any thoughts or ideas will be much appreciated

[v-jayw-msft]

Hi @Chalk442 ,

Could you add an index column so that you could use the index column in your formula instead of using datetime column. And the index column won't be duplicated even though there's duplicate dates.

Best regards,

Jay

[amitchandak]

@Chalk442 , create borndate  without time a new column.

borndate = [bornTime].date
or
borndate = date(year([bornTime]),month([bornTime]),day([bornTime]))

have a date table and join borndate  with that. In that tbale have column like, day, month, Quarter and year. Create hierarchy on those if needed

Try a measure like

Measure:
%CHANGE =
VAR CurrentValue = [Count of numberOfCatsbyBreed]
VAR PreviousValue =
SWITCH(

TRUE(),
ISINSCOPE('Date'[DAY]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Date'[Date], -1, DAY)),

ISINSCOPE('Date'[MONTH]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Date'[Date], -1, MONTH)),
ISINSCOPE('Date'[QUARTER]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Date'[Date], -1, QUARTER)),
ISINSCOPE('Date'[YEAR]), CALCULATE([Count of numberOfCatsbyBreed], DATEADD('Date'[Date], -1, YEAR))

)

RETURN

DIVIDE(

CurrentValue - PreviousValue,

PreviousValue

)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.