
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to properly use addmonths, monthstart and yearstart?
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!!


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
