DATEADD return blank

[Meng]

Hi,

Question1:

I need to count customer number of last one year ,also the last two year, while I find it will get the same result data when I used the DAX below, while in fact, the data should be different for the two years.

S_CountCustomer_LastTwoYear = CALCULATE(DISTINCTCOUNT(s_salesorder[s_erp_actualcustomerName]),DATEADD('Date'[Date].[Date],-2,Year))

S_CountCustomer_LastYear = CALCULATE(DISTINCTCOUNT(s_salesorder[s_erp_actualcustomerName]),SAMEPERIODLASTYEAR('Date'[Date].[Date]))

or

S_CountCustomer_LastYear = CALCULATE(DISTINCTCOUNT(s_salesorder[s_erp_actualcustomerName]),DATEADD('Date'[Date].[Date],-1,Year))

Also, if I use 'Date'[Date] instead of 'Date'[Date].[Date] in DATEADD function, it will occur the error like below. It reminds us that DATEADD needs continue date option,while the table 'Date' is a calendar table using formula "DATE=CALENDARAUTO()",and the calendar table has relationship with fact table on date.

Question2:

I need to get total amount last year and last two year, used the formulas like below , while it returned blank. How to get correct result.

S_totalamount_CurrentYear = CALCULATE(DIVIDE(SUM(s_salesorder[s_totalamount_Base]),1000),DATESYTD('Date'[Date]))   ----it is not blank, you see.

S_totalamount_LastYear = CALCULATE(DIVIDE(SUM(s_salesorder[s_totalamount_Base]),1000),DATESYTD(DATEADD('Date'[Date].[Date],-1,YEAR)))

S_totalamount_LastTwoYear = CALCULATE(DIVIDE(SUM(s_salesorder[s_totalamount_Base]),1000),DATESYTD(DATEADD('Date'[Date].[Date],-2,YEAR)))

Thank you all, meme.

[v-easonf-msft]

Hi， @Meng 

Could you please tell me whether your problem has been solved?
If yes, you could accept the helpful answer  or  your own reply as solution

Others having similar concern can find the answer more easily.

Best Regards,
Community Support Team _ Eason

[Meng · Accepted solution]

Finally, I put the Year of Date[Date] into column box of Matrix visua, it will return the data in last Year and in last two Year according to related years. it seems to mean that the Date must also be added into visual when use Time Intelligence function such as DATEADD.

[amitchandak]

@Meng , this formula is for second last year. Also do not use .date. Make sure you have dates for last 2 years and date table is marked as date table.

CALCULATE(DISTINCTCOUNT(scw_salesorder[scw_erp_actualcustomerName]),DATEADD('Date'[Date],-2,Year))

or 2 years including this

Rolling 2 = CALCULATE(DISTINCTCOUNT(scw_salesorder[scw_erp_actualcustomerName]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-2,Year))

[Meng]

Hi @amitchandak ,

Thank your for the help.

while i need to get the data in last two year, instead of the data in two range year, so the DATESINPERIOD is not useful. for example, the current year is 2020, the formula [Scw_totalamount_LastTwoYear ] is used to get data in 2018, not from 2018 to 2020.   and the fact table have at least three years data.

also, i make the calendar table as date table, the formula will be ok, while the table visual will occur error that DATEADD need continue date option still.

and i think the Calendar table is created using CALENDARAUTO(),which is a date table having unique and continue date options.

[v-easonf-msft]

Hi , @Meng 

Have you created a relationship between table "Date" and table "scw_salesorder" ?
If possible,please share a screenshot about your table relationship in table model view.

Best regards,

Eason