How to Pull out MTD (month to date) data

[ll1100000]

I have a daily budget table for the entire year 2017 as below.

Date	Budget
1/1/2017	100
1/2/2017	200
1/3/2017	300
1/4/2017	400
1/5/2017	500
1/6/2017	600
Continue
12/31/2017

How can I set up a way to only pull out the Month to date information  

For example, today is June 26th, 2017, so only pull out the budget from June 1st to June 26th

Then when it move to July 1st, it will pull out July's MTD number....

Since I will update this report on a daily basis, I think the formula is something like

Between Date Time first of month() and Today()

So when I update this report tomorrow, which is June 27th, 2017, it will pull out the data from June 1st up to June 27th.

Thank you, 

[DanS · Accepted solution]

You should be able to use the DateTime and DateFilter tool. 

This will format the date and allow you to filter based on start and end date. 

It would require you to change the filter. So it wouldn't be completely automated, but if you aren't scheduling it to run, this should work. 

Thanks,

Dan 

[BenMoss]

Hi!

Hopefully I can provide a relatively simple solution for you.

One of the functions available within Alteryx is datetimenow() (which essentially returns the date time to the second).

We can use this, in tandem with a datetimetrim function to acknowledge dates that are the same month/year as todays date.

You would also then add a second condition to ensure that all values are less than todays date to make sure we don't bring through future values from the same month/year.

[ll1100000]

Hi BenMoss;

Thank you for your reply... I am having some issues for using your methods since I can't get any data out.

I keyed in this formula into the filter function

DateTimeTrim(Date,'month')=DateTimeTrim(dateTimeNow(), 'month')

and [Date]< datetimenow()

Also, I don't see how this formula tells to pull out the data from the 1st date of the current month.

I only need to pull out the MTD data, not all data that before datetimenow()

Thank you.

[ll1100000]

Hi DanS;

Yes, your solution works great!  That is the way I am doing now :)

but I still want to set up it automatically since I have to run this every month...

Although it only takes one second to adjust on the calendar, I wish to create something that only needs to click the run bottom 1 time to run every thing. 

Thank you.

[DanS]

Glad it's working!

Ben had the right idea for scheduling/running automatically with the DateTimeTrim. 

I attached a workflow that combined both of ours and it should get your desired MTD output. 

This should automatically parse your Date format and run through his filter.

[zwiskur]

Hi, Ben

I can’t open your link. Would you please type in the solution of how to pull MTD data?

Thank you

[zwiskur]

Hi, DanS

Looks like you have an automatic solution. Please type it in. I can’t open your link.

Thank you