Wednesday, August 5, 2020

How to find and get quarter / quarter name (month / month name) from a given date in Power BI

Let's imagine that we have General ledger attributes table that contains Date field

You would like to extract Years, Quarters, Months values from the Date field. For this purpose you can create new columns (Month, Quarter and Year) and use the following DAX expressions:

Month = MONTH('General ledger attributes'[Date])
Quarter = QUARTER('General ledger attributes'[Date])
Year = Year('General ledger attributes'[Date])

The issue you might experience is that Month column will return numeric values instead of text (like 1,2,3,4,....12). Quarter column will return numeric values also (like 1,2,3,4).

What if you want to have text values. For Quarters, for instance, to have Q1, Q2, Q3 and Q4. For Months, to have January, February etc.

You may try to use the following expression:
FORMAT('General ledger attributes'[Date], "MMM")

If it doesn't work as it was in my case, because of the following error message
Function 'FORMAT' is not allowed as part of calculated column DAX expressions on DirectQuery models.
you can create additional columns and use the following expressions:

MonthName =
SWITCH('General ledger attributes'[Month], 1,"January", 2,"February",3,"March",4,"April",
5,"May",6,"June",7,"Jul",8,"August",9,"September",10,"October",11,"November",12,"December")

QuarterName = SWITCH('General ledger attributes'[Quarter], 1,"Q1", 02,"Q2",3,"Q3",4,"Q4")

The structure of the columns will be the following:

Now you can build visualizations using new created columns.

Monday, July 27, 2020

Batch Management Service cannot be started

If you experience the following issue while starting Microsoft Dynamics 365 Unified Operations: Batch Management Service:

Windows could not start the Microsoft Dynamics 365 for Operations - Batch Management Service service on Local Computer.
Error 1064:An Exception occurred in the service when handling the control request.

The reason might be because your environment is set to Maintenance mode.

To change this, execute the following script:

update SQLSYSTEMVARIABLES SET VALUE = 0 where PARM = 'CONFIGURATIONMODE'

Afterwards, do not forget to restart IIS by running the following command:

C:\windows\system32> iisreset

Friday, July 24, 2020

How to turn on maintenance mode through SQL in Microsoft Dynamics 365 F&SCM

In order to turn on maintenance mode in Microsoft Dynamics 365 F&SCM through SQL, the following steps needs to be performed:
  1. Open Microsoft SQL Server Management Studio 
  2. Connect to the Server
  3. Expand AxDB database and execute the following script

update SQLSYSTEMVARIABLES SET VALUE = 1 where PARM = 'CONFIGURATIONMODE'

       See the result of the update:

select * from SQLSYSTEMVARIABLES

    After update, execute iisreset command in cmd. 

    To return back execute the following query:

update SQLSYSTEMVARIABLES SET VALUE = 0 where PARM = 'CONFIGURATIONMODE'

Tuesday, March 31, 2020

High-level changes within Small parcel shipping (SPS) feature in Microsoft Dynamics 365 F&SCM

Within this article I would like to provide with the high-level changes that were made within Small parcel shipping functionality.

1.  Container weight configuration
     Transportation management à Setup à Carriers à Shipping carriers
     Average container weight field

2.  Carrier account assignment
     Transportation management à Setup à Rating à Carrier service account

3. Customer carrier account assignment
    Accounts receivable à Customers à All customers à General tab à Carrier customer accounts

4. Carrier label printing configuration
    Warehouse management à Setup à Containers à Container packing policy à Carrier label printing section
     Upon Container Close, label will be printed that is stored on the Route Segment Container if:

  •  Print Container Shipping Label Rule is not Never
  •  Manifest Requirements For Container is set to Transportation Management 
  •  Container manifest id is specified on the container

5. Print shipping label
    Warehouse management à Packing and containerization à Containers à Shipping label
     Label will be printed that is stored on the Route Segment Container.
     The system will try to find Route Segment Container by Container ID and Container manifest ID (ShipCarrierTrackingNum). Once found, there is a field on the Route Segment Container contained label that will be printed. That field is called ContainerShippingLabel (is not visible from UI)

6. Carrier customer account assignment per sales order
    Accounts receivable à Orders à All Sales orders à Transportation tab à Carrier customer account field

Friday, March 27, 2020

Responsibility center is blank while creating Budget Plan in Microsoft Dynamics 365 F&SCM


You may experience an issue with the Responsibility center lookup while creating Budget plan.

If you have Security model = Based on security organizations in the Budget Planning Configuration (Budgeting > Setup > Budget planning > Budget planning configuration), follow the steps below to fix this issue:

1. Go to the Worker who is creating Budget plan (Human resources > Workers > Workers).
2. Click Work Position Assignment in the Action pane.
3. Drill down to Positions  clicking on the Hyperlink.
4. Check the Department value. Note department value.
5. Go to Organization administration > Organization > Operating units.
6. Select the Department of the worker noted in the step 4.
7. Make sure that “In hierarchy” toggle is turned on.
8. Fill in Manager field, if empty.
9. Go to Organization administration > Organization > Organization hierarchy.
10. Select the hierarchy that is used for the budget planning process for which budget plan is being created.
11. Make sure that Security purpose is assigned.
12. Make sure that department noted in the step 4 is included into the hierarchy.
13. Go to System administration > Users. Find user who is creating budget plan. Make sure that Personnel field is filled in by worker value used in step 1.
14. Make sure that user (from step 13) has Budgeting role assigned.
15. Click Assign organizations button. Make sure that access is granted to hierarchy noted in step 10.

Try to create budget plan. I hope it solved the issue.

Thursday, March 26, 2020

Turn on new Small parcel shipping (SPS) feature in Microsoft Dynamics 365 F&SCM

Within 10.0.9 Application release, Platform Update 33 (PU33) Small parcel shipping (SPS) feature has been added. In order to activate this feature you would need to complete the following steps:
  1. Insert Flight name (aka Feature name) into SYSFLIGHTING
  2. Restart IIS
  3. Check for updates in the Feature management 
  4. Enable Small parcel shipping (SPS) in the Feature management 
Step 1:  Insert Flight name into SYSFLIGHTING

Open SQL Server Management Studio as Administrator and execute the following SQL statement to insert the TMSSmallParcelShippingFeature flight into the SYSFLIGHTING table.

insert into dbo.SYSFLIGHTING(FLIGHTNAME, ENABLED, FLIGHTSERVICEID) values ('TMSSmallParcelShippingFeature', 1, 12719367)

Step 2: Restart IIS

Open command-line(cmd) as System administrator and at the command prompt, type:

iisreset

Step 3:  Check for updates in the Feature management

Navigate to System administration > Workspaces > Feature management 
Click Check for updates button

Step 4:  Enable Small parcel shipping (SPS) in the Feature management 

Find Small Parcel Shipping feature within Transportation management module and click Enable button.

Friday, March 6, 2020

Due date alerts batch job is not working in Microsoft Dynamics 365 F&SCM (Platform update 31)

With the Platform update 31, Platform update 32, you may experience the issue with Due date alerts batch job (System administration > Periodic tasks > Alerts > Due date alerts).

In general, Due date alerts batch job is in Executing status but does nothing, it just gets stacked.

The result of this is that Microsoft Dynamics 365 Unified Operations: Batch Management Service service is terminating unexpectedly and restarting every 2 mins.

Having investigated Due date alert batch job, the root cause was found. The system fails here:


As a workaround in order to fix it, you could set 2 weeks for the Alert rule instead of “is due in 1 calendar day”.

I do know that it’s quite an odd solution if you are using Due date alerts constantly.

The good news is that Microsoft released a hotfix already for this issue.

Platform 31 - KB 4537849 Stack overflow caused by recursion 
https://fix.lcs.dynamics.com/Issue/Details/1277393?kb=4537849&bugId=408330&dbType=3&qc=1efe704342b29b568e9306fead37964410dd0958d71da87fbcb070612a53c21b

Platform 32 - KB 4537705 Stack overflow caused by recursion
https://fix.lcs.dynamics.com/Issue/Details/1277393?kb=4537705&bugId=407923&dbType=3&qc=1efe704342b29b568e9306fead37964410dd0958d71da87fbcb070612a53c21b

You can use the link below to install these hotfixes: https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/migration-upgrade/download-hotfix-lcs