Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Bob Bartlett]

Looking to see if there is a formula that will automatically populate the correct date range in the Open Orders This Month field on the filter below.  This filter needs to take the date the report runs and show the first day of the next month plus the lookback indicator “..”.  For example today is 1/20/2017 so that field should read “..020117”.  I found a formula that will automatically establish the “020117” but can’t figure out how to add the “..” in front of it.  You can see the fomula I’m using in the fx box.

——————————
Bob Bartlett
Tifosi Optics, Inc
Watkinsville GA
——————————

[Rob Fontaine]

Hi Bob,

Try the following:

=NP(“DateFilter”,,[your formula])

Regards,
Rob

——————————
Rob Fontaine
Director, ERP & Financial Analysis
SilverBirch Hotels & Resorts
Vancouver BC
——————————
——————————————-

[Bob Bartlett]

Thanks guys.  I’m out of the office this week but will give it a try when I return.

 

Bob Bartlett

Operations Manager

Tifosi Optics, Inc.

1011 Industrial Blvd.

Watkinsville, GA 30677

866-310-0996

800-229-8122 Fax

 

Click on link below to view our 2017 Catalog

http://files.tifosioptics.com/Catalog/2017/2017_catalog_LowRes.pdf

 

CONFIDENTIALITY NOTICE

The information contained in this email transmission and any attachment is confidential and remains the property of Tifosi Optics, Inc. until it is received by the intended recipient. If you are not the intended recipient, please be advised that any use, including further transmission or disclosure of this communication, is strictly and expressly prohibited. If you have received this communication in error, please notify bob@tifosioptics.com as soon as possible, and delete it from your computer without making and/or retaining any copies. Thank you for your cooperation.

 

——Original Message——

Hi Bob,

Try the following:

=NP(“DateFilter”,,[your formula])

Regards,
Rob

——————————
Rob Fontaine
Director, ERP & Financial Analysis
SilverBirch Hotels & Resorts
Vancouver BC
——————————

[Ramin Marghi]

Hi Bob,

What Rob mentioned is the ideal solution, but I thought I’d add to the discussion an alternative as well, since with Jet Reports, there’s almost always a number of ways you can accomplish things — either using Excel functionality or the Jet functions.

Excel Functionality
The first day of the next month would essentially be:

=DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

And to add the “..” before it, you could very well do:

=”..”&DATE(YEAR(TODAY()),MONTH(TODAY())+1,1)

Now this will show you the Excel/Julian date (see screenshot below), which Jet understands just fine, so that technically does the trick:

If you wanted it to be masked, you could also add the TEXT function to show mm/dd/yyyy:

Bonus:
If you set up your Accounting Periods as months, you could also do something like this instead:

Finally, as Rob already mentioned, you could append the NP(“Datefilter”) and leave the first parameter blank to get an open-ended start date:

——————————
Ramin Marghi , CPA, CGA
NAV Finance Consultant
Catapult
VANCOUVER BC
——————————
——————————————-