Sameperiod last year gives different results with different date tables

anee123 — Sat, 23 Jan 2021 07:32:29 GMT

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

Re: Sameperiod last year gives different results with different date tables

lbendlin — Sun, 24 Jan 2021 23:58:42 GMT

That's not how SAMEPERIODLASTYEAR works. It will always use complete periods. If you want YoYTD logic you need to add that filter yourself.

Re: Sameperiod last year gives different results with different date tables

Anonymous — Mon, 25 Jan 2021 07:11:53 GMT

Hello anee123
You can refer to this link

topic Re: Sameperiod last year gives different results with different date tables in DAX Commands and Tips

Sameperiod last year gives different results with different date tables

Re: Sameperiod last year gives different results with different date tables

Re: Sameperiod last year gives different results with different date tables