DAX Measure - Distinct Count YTD Values

[Anonymous]

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!!

[timg]

Hi cy115,

Alternatively, this could be a solution as well:

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

 

Regards,

Tim

[Anonymous]

Hi Tim, 

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

[timg]

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

[Anonymous]

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

[amitchandak · Accepted solution]

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

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]

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

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.

[Anonymous]

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