How to Pull out MTD (month to date) data

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,

Common Use Cases

Accepted answers

DanS

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

All comments

DanS

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.

MTD Dates.yxzp

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703