when get same period of last year, if the date is not applicable, how to make it blank

[Anonymous]

i tried to get the number of rows from the same period of last year, and use this measure:

target = CALCULATE(COUNTROWS('leads final'), datesbetween('leads final'[modifiedon (bins)], FIRSTDATE(DATEADD('leads final'[modifiedon (bins)], -12, MONTH)),LASTDATE(DATEADD('leads final'[modifiedon (bins)], -12, MONTH))))

i know that lastDate and FirstDate will return the most latest/earliest date when the formula is not applicable. i do not want that happen, just showing blank. 

can anyone please help me out?

thank you 

[Anonymous]

Datesbetween is a time-intelligence function, and as such requires a dedicated date table to work correctly. Do you have a data table?

[Anonymous]

Nope. all the fields are from the same table. Do i have to create another data table to connect with my original table?

thanks

[Anonymous · Accepted solution]

Yes, you would want to create a date table and relate that to your main table. You can create a calendar easily using CALENDARAUTO() or the CALENDAR functions when creating a caclulated table. 

https://docs.microsoft.com/en-us/dax/calendar-function-dax

Then from there you want to be sure to mark that as date table:

[Anonymous]

thanks! i saw this kind of method somewhere when we create KPI. may i ask why we need this date table?

thanks

[Ashish_Mathur]

Hi,

With a Calendar Table in place, you will be able to use the Date and Time Intellignece functions of DAX with ease.