Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!!
Solved! Go to 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.
Hi cy115,
Alternatively, this could be a solution as well:
OrdersYTD =
TOTALYTD(DISTINCTCOUNT(FactSales[SalesOrderLineKey]), 'DimDate'[Date])
Regards,
Tim
Proud to be a Super User!
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
Regards,
Tim
Proud to be a Super User!
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]))
@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.
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)
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.
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"))
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |