Time intelligence not returning correct value

[stxreporting]

Hi,

I'm using a dateadd function that calculates the previous period's value using a separate date table.

It was brought to my attention by a user that the values for 2020 are not correct. I figured out its because I'm using the following expression which doesn't work with leap years:

Calculate([some measure],DATEADD('Date'[Date],-365,DAY))

This works perfectly except when it's a leap year. 

In order to fix this I tried the following:

Calculate([some measure],DATEADD('Date'[Date],-1,YEAR))

For some reason it is includes the values for the whole month of August last year instead of only taking into account the 9 days that we have so far this month. The date table I'm using is continuous (no gaps) and includes all the dates.

Could someone please explain what I am doing wrong?

Many thanks,

Chris 

[amitchandak]

@stxreporting , That will happen if you use month year in visual, in that case it is getting complete month dates

Try like

forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, Calculate([some measure],DATEADD('Date'[Date],-1,YEAR)), blank())
//or
//calculate(Calculate([some measure],DATEADD('Date'[Date],-1,YEAR)),filter('Date','Date'[Date]<=_max))

[stxreporting]

Hi,

I do not have month year in my visual, just the measures for current year and for passed year (the one I sent above). I don't see how the solution you proposed will return anything other than blank(), since the max('date'[date]') will by definition always be bigger than _max right?

Could you please elaborate?

Thanks,

Chris

[v-easonf-msft]

Hi, @stxreporting 

Please try formula like:

Result=
 Calculate([some measure],DATEADD('Date'[Date],-1,YEAR),'Table'[Monthnumber]<currentYearMaxMonth)

or apply a filter measure to table visual filter pane.

Best Regards,
Community Support Team _ Easonl

[stxreporting]

Hi @v-easonf-msft,

thanks for your reply but this doesn't seem to change anything?

Could you please elaborate what this part of your solution does?

'Table'[Monthnumber]<currentYearMaxMonth

how I see it that would still not be able to retrieve the days passed this month so far?

Please let me know,

Chris