Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Measure - Distinct Count YTD Values

Hi!   I'm trying to find the distinct number of times a customer places an order YTD.    So far I have this formula which is giving me a discount count of ALL of that customers orders up to today...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , have tried datesytd with date table ?

    YTD Sales = CALCULATE(DISTINCTCOUNT(AS400_Transactions[Order_Date Recalc]),DATESYTD('Date'[Date],"12/31"))

     

    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.

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , have tried datesytd with date table ?

YTD Sales = CALCULATE(DISTINCTCOUNT(AS400_Transactions[Order_Date Recalc]),DATESYTD('Date'[Date],"12/31"))

 

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

THANK YOU so much! That did work! If I want to do it for all of 2020, should I just do SAMEPERIODLASTYEAR in place of DATESYTD? 

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , try like

     

    Last YTD Sales = CALCULATE(DISTINCTCOUNT(AS400_Transactions[Order_Date Recalc]),,DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

     

    example to restrict

     

    LYTD QTY forced=
    var _max = date(year(today())-1,month(today()),day(today()))
    return
    if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
    //OR
    //CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
    //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hmm, this didn't seem to work for YTD-1. Now its filling in a minimum of "1" value for every single customer even if they didnt purchase that year at all. 

      =CALCULATE(DISTINCTCOUNT(AS400_Transactions[Order_Date Recalc]), DATESYTD(DATEADD('Calendar Transaction'[Date],-1,YEAR),"12/31"))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi amitchandak, 

       

      Thanks so much for your help so far. For YTD-1, i just tried this formula that you suggested above: 

      =CALCULATE(DISTINCTCOUNT(AS400_Transactions[ORDER_DATE],DATESYTD(DATEADD('Calendar Transaction'[Date],-1,Year),"12/31") )) but it is showing errors.

       

      I also tried the restrictions but its not working either.