Forum Discussion

Meng's avatar
Meng
Advocate II
5 years ago
Solved

DATEADD return blank

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 shoul...
Need Help
  • Meng's avatar
    Meng
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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's avatar
Meng
Advocate II
5 years ago

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's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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