How to create KPI measure in Power BI

If you want to build KPI visuals as it is presented on the screen below, you can achieve this by adding new measure and use the following DAX expression

KPI =

SWITCH (

TRUE (),

[Profit/Loss] > 0, unichar(9650),

[Profit/Loss] < 0, unichar(9660)

)

If Profit/Loss value is negative, you will have black down-pointing small triangle. If Profit/Loss is positive, you will have black up-pointing small triangle.

If you want to have different colors based on the values, you may use Conditional formatting as it is presented below.

How to apply Regional settings from the Source data formats to the exported data through Data management

As you might know, there is a possibility to setup Regional settings on the Source data format in the Data management. 

Unfortunately those settings are not applied automatically. The important thing to make it work is the following:

When create Export project and add entity to the project, set Skip staging toggle to NO. Only in this case, Regional settings will work.

How to use grouping in Power BI and apply group to the DAX expression

 Let's say you have field that contains the fixed list of values and you want to group them. In my scenario I have TypeID field that contains the following values: Revenue, Liability, Equity, ProfitandLoss, Expense, BalanceSheet, Asset, Total. In my case, Revenue, Liability, Equity corresponds to Credit and the rest to Debit. What can I do? 

1. Highlight the TypeID, right-click and choose New group
2. Set the Group name (TYPEIDGROUP for example)
3. Select Revenue, Liability, Equity values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Credit name
4. Select remaining values in the Ungrouped values section and click Group button. Rename group in the Group and members section and set Debit name. 
5. The result should be as presented on the screen

Now, you are able to connect to the group and use it in the formula. For example, I used it in the created measure:

Actuals credits positive =

CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],

FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Debit" )

- CALCULATE ( FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actuals],

FinancialReportingTransactionData_FinancialReportingAccount[TYPEIDGROUP] = "Credit")

Another option is to use IN expression

Labor =

CALCULATE

(

    FinancialReportingTransactionDataV2_FinancialReportingFactWithSDF[Actual operating]*-1,

FinancialReportingTransactionData_MainAccount[VALUE] in {"600300", "600400"}

)

It's up to you to decide which approach to use.  

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.

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

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'

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