How to properly use addmonths, monthstart and yearstart?

[neena123]

Hello, I am trying to create a date range by using addmonths, month start and year start. I don't think I have properly grasped how addmonths is supposed to work. This is what I am working with now:

LET WorkDay = ((year(addmonths(today(),-1))-1900)*1000)+round((monthstart(addmonths(today(),-1)))-(yearstart(addmonths(today(),-1))))+1;

What I want to pull is current year YTD and previous year YTD. But I am not getting last year at all. I am getting just this month's (July) data and last month's (June) data. Any help is appreciated. Thanks!!

[MarioCenteno]

Hi! neena123

Will this example work for you?

Prev Year

SUM({$<[Date]=

{'>=$(=AddMonths(YearStart(Today()),-12))<=$(=AddMonths(MonthEnd (Today()),-12))'}>}[Field])

Current Year

SUM({$<[Date]=

{'>=$(=AddMonths(YearStart(Today()),0))<=$(=AddMonths(MonthEnd (Today()),0))'}>}[Field])

Mario Centeno

[neena123]

Hi Mario,

Thanks for your reply!! I am trying to set a variable in my script so when I pull in my files I just set it to the script expression I have set above. If that makes sense. I pull my data from Oracle so I have to work with the julian date.

[MarioCenteno]

You must declare the variables when loading the data with a specific date of your script.

Example

let vMaxDate  = '=max(date_example)';

let vMaxDay   = '=day(max(date_example))';

let vMaxMonth  = '=month(max(date_example))';

let vMaxYear  = '=max(Year)';

let vPriorMonth  = '=month(addmonths(max(date_example),-1))';

let vPriorMonthYear = '=Year(addmonths(max(date_example),-1))';

let vPriorYear  = '=vMaxYear-1';

let vPriorYearDate = '=date(addyears(max(date_example),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

[table]:

Load

Year([date_example]) as Year,

  Month(date_example) as Month,

  Day(date_example) as Day,

  Date(monthstart(date_example), 'MM-YYYY') as MonthYear;

select date_example from table;