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: 

=CALCULATE(DISTINCTCOUNT(AS400_Transactions[Order_Date Recalc]), AS400_Transactions[Order_Date Recalc]<= DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))) 

 

I don't know what to add so that it only counts YTD and not everything in the past. Order_Date Recalc is just all the order dates tied to customer IDs for the past 3 years. 

 

Thank you!!

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.

9 Replies

  • 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)

  • timg's avatar
    timg
    Solution Sage
    5 years ago

    Hi cy115,

     

    Alternatively, this could be a solution as well:

    OrdersYTD = 
TOTALYTD(DISTINCTCOUNT(FactSales[SalesOrderLineKey]), 'DimDate'[Date])

     

     

    Regards,

     

    Tim

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Tim, 

       

      That worked too - thank you! Using this method, how would I look at how many transactions happened for FY 2020? 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I just did this and it seems to work! It does seem to be lining up with my data but if you have a moment would love a confirmation from you as I'm pretty new to DAX. 

        =TOTALYTD(DISTINCTCOUNT(AS400_Transactions[ORDER_DATE]),SAMEPERIODLASTYEAR('Calendar Transaction'[Date]))