Hi,
I have date range from Jan 1 2020 till Dec 31 2021.... when the date is 23/1/2021(today) I should see distinct count of 2021 (Jan1-jan23 2021) and compare to prev year(Jan1 2020- Jan 23 2021).... when the month slicer is feb, mar or any other month apart from Jan.. the YTD should be 0 because it does not have data after Jan 23 2021 but for prev year feb 2020 has data and pytd should show data. I am able to achieve this using different date tables and using same period last year.
I want to achieve this with the Activation Date field present in sheet1 data instead of creating a seperate date field, I am getting partial output but some of the cases, its throwing error due to dates discontinuity. I used MAX and min of the date and created a date table but that gives data from 2019 till date, I want to avoid 2019 data while selecting, I don't want to use visual level filters but instead in dax. in the max min table the date range of activation date should be from 1-jan-2020 till 31-dec-2021.
Please find the pbix file below and the formula used,PYTD3 is giving me correct result which I want to achieve this using sheet1 activation date column and Maxmindatetable date column.
YTD = CALCULATE(DISTINCTCOUNT(Sheet1[ID]),DATESBETWEEN('CalendarDatetable'[Date],date(2021,01,01),date(2021,12,31)))
PYTD1 = CALCULATE(DISTINCTCOUNT(Sheet1[ID]),SAMEPERIODLASTYEAR('Maxmindatetable'[Date]))
PYTD2 = CALCULATE(DISTINCTCOUNT(Sheet1[ID]),SAMEPERIODLASTYEAR(Sheet1[Activation Date]))
PYTD3 = CALCULATE(DISTINCTCOUNT(Sheet1[ID]),SAMEPERIODLASTYEAR(CalendarDatetable[Date]))
https://drive.google.com/file/d/1I-VmtFK-XQU6-N1ijt8awAVzTUkxa5Er/view?usp=sharing
