web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / SmartList Builder date...
Microsoft Dynamics GP (Archived)

SmartList Builder date calculation

(0) ShareShare
ReportReport
Posted on by Community Member

SmartList Builder: date calculation current month/same month previous year

I am using this function to get the sales for a certain month: CASE WHEN MONTH ( {Sales Transaction History:Document Date} ) =1 THEN {Sales Transaction Amounts History:Total YTD} ELSE 0 END How can I get the result of the same month from the previous year in another column???

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Mahmoud Saadi Profile Picture
    Mahmoud Saadi 32,738 on at
    RE: SmartList Builder date calculation

    There are a variety of functions through which you can reach your target. Initially, you should calculate the previous month of the previous year as a parameter to be passed for the (case) function. your data set should have either a full date field under one column or two columns (Year and Month)

    • In case you have a date field, consider the date function, Date Part 
    • In case you already have the month and the year in two separate columns, you need to simply subtract (one) from the year to get the previous year. The month remains the same

    Now you can proceed with the condition function. 

    CASE {Table1:Field1}
    WHEN value1 THEN {Table1:Field1} + {Table1:Field2}
    WHEN value2 THEN {Table1:Field1} + {Table1:Field3}
    ELSE {Table1:Field1} + {Table1:Field4}
    END

    As a reference for your task, you can refer to very useful support article from Microsoft on a list of functions that can be used with Smart List builder

    To save yourself a lot of headache, you can create a custome SQL view on the database level, to be published to Dynamics GP without further manipulation on the Smart List builder. You can make use of Victoria Yudin SQL view SQL view for SOP sales by customer by month

    Your feedback is highly appreciated, 

  • Community Member Profile Picture
    Community Member on at
    RE: SmartList Builder date calculation

    Here are a couple different ways of doing the calculation to do what you need:

    1. CASE WHEN MONTH ( {Sales Transaction History:Document Date} ) = 1 and ((YEAR ( GETDATE() ) - 1) =  YEAR ( {Sales Transaction History:Document Date} )) THEN {Sales Transaction Amounts History:Total YTD}  ELSE 0 END

    2.CASE  

    WHEN MONTH ( {Sales Transaction History:Document Date})=1  THEN

    CASE YEAR (GETDATE() )-1

    WHEN YEAR ({Sales Transaction History:Document Date} ) THEN    {Sales Transaction Amounts History:Total YTD}

    ELSE 0

    END

    ELSE 0

    END

  • Community Member Profile Picture
    Community Member on at
    RE: SmartList Builder date calculation

    Have to select date range 1-1-2013 to 12-31-2014 in SmartList, otherwise no result for January prior year. Result of prior year month January is correct, but in current year totals for January 2013 and January 2014  added up together per month and also for the other months.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans