Forum Discussion
Creating Measures for distinct count between 2 custom dates
I have a dataset of leads with unique IDs and lead dates for the past 2 years. However, the years are not based on the calendar year, but a custom time frame 8/1 - 7/31. I also created a date table.
I would like to create measures that do the following -
Prior Year leads - distinct count of IDs for lead date between 8/1/19 - 7/31/2020
Current Year leads - distinct count of IDs for lead date between 8/1/20 - 7/31/2021
Prior Year Leads to date - count of prior year leads up to current date
I was able to do this with the data separated into 2 queries using DAX below, but was unsure how to accomplish it with mixed data set.
2 Replies
ncondon22 , YTD allows your the end date of your choice
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"7/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"7/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"7/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"7/31"))to get calendar of your choice
Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uAThank you this was helpful, especially the date table set up. However I am still running into an issue because I am not using the SUM function on numbers, I am actually counting records on the dataset.
CONTACT_ID Lead Date abcd 08/15/19 efgh 06/28/20 ijkl 08/15/20 mnop 04/01/2
In this case I am trying to count distinct IDs on current year (8/1/2020 - 7/31/2021),previous year, and previous year to date.
My expected outcome would be -
Current Year - 2
Previous Year - 2
Previous YTD - 1
Thanks for your help
