topic Re: Creating Measures for distinct count between 2 custom dates in DAX Commands and Tips

Creating Measures for distinct count between 2 custom dates

ncondon22 — Wed, 21 Apr 2021 14:05:07 GMT

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.

Total CY Leads = COUNTA(CY_Leads[CONTACTID])

Total PY Leads = COUNTA(PY_Leads[CONTACTID])

Thank you

Re: Creating Measures for distinct count between 2 custom dates

amitchandak — Wed, 21 Apr 2021 14:13:32 GMT

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/1187441

Power 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_0uA

Re: Creating Measures for distinct count between 2 custom dates

ncondon22 — Wed, 21 Apr 2021 17:55:46 GMT

Thank 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