Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

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

View solution in original post

9 REPLIES 9
timg
Solution Sage
Solution Sage

Hi cy115,

 

Alternatively, this could be a solution as well:

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

 1.PNG

 

Regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Hi Tim, 

 

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

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

1.PNG

 

Regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Super User
Super User

@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
Not applicable

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? 

@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
Not applicable

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.