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.

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

    • timg's avatar
      timg
      Solution Sage
      5 years ago

      Hi cy115,

      The TOTALYTD function allows you to customize the year end with additional input to allow for FY and such (image 1).

      SQLBI has some more in-depth info regarding this so I'd check this article out for some more details if you'd like to try that method: The hidden secrets of TOTALYTD - SQLBI

       

      Regards,

       

      Tim