cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

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 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

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

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:

How to Mark as Date Table.png

View solution in original post

5 REPLIES 5
Highlighted
Anonymous
Not applicable

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

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

Highlighted
Helper I
Helper I

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

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

 

thanks

Highlighted
Anonymous
Not applicable

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

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:

How to Mark as Date Table.png

View solution in original post

Highlighted
Helper I
Helper I

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

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

 

thanks

Highlighted
Super User IV
Super User IV

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors