Commitment types in the Sales/Purchase agreements in Microsoft Dynamics 365 F&SCM

Default commitment type must be selected upon sales/purchase agreements creation. Each option is defined below, but generally speaking, an agreement needs to differentiate between value (price) and quantity.

The commitment type indicates whether you agree to purchase a specific quantity of products or products for a specific amount from the vendor. If you change the default commitment type, and then create a new line, the line is defined by the commitment type that you selected. 

Product quantity commitment: You agree to purchase a specific quantity of products; The lines that use this commitment type are defined by an item number, and the quantity and unit that were agreed on; The Amount field is not editable. 

Product value commitment: You agree to purchase products for a specific amount; The lines that use this commitment type are defined by an item number and the amount that was agreed on; The Quantity and Unit fields are not available. 

Product category value commitment: You agree to purchase for a specific amount and from a specific procurement category; The lines that use this commitment type are defined by a procurement category in the hierarchy of procurement categories and an amount; The Quantity and Unit fields are not available. 

Value commitment: You agree to purchase for a specific amount, and from a top-level procurement category or the category's subordinate categories; The lines that use this commitment type are defined by the top-level procurement category in the hierarchy of procurement categories and an amount; The Quantity and Unit fields are not available.

Data cache clean up in Microsoft Dynamics 365 F&SCM

In addition to the ways described here: https://gfeaxblog.wordpress.com/2017/12/14/ax-2012-clear-the-cache/

you have a couple of additional options to refresh the cache

#1 Run the class from browser

https://Environment name/?cmp=Company name&mi=SysClassRunner&cls=SysFlushAOD

Note: Please replace the text highlighted with your environment data

#2 Run Data cache clean up from the System parameters

System administration > Setup > Data cache > Data cache parameters > Cache refresh tab

PowerBI embedded in Tier1 and Tier2 environments

What is the proper way to access the data tables for Power BI in Production, Sandbox, etc?  

If you need to connect PowerBI to Microsoft Dynamics 365 for Finance and Operations you have several options depends on the environment that you are using.

Tier2 (PROD/UAT/TEST) or Tier1 (developer onebox)

• Tier2 environments have Azure SQL AXDW database which can be configured for automatic updates using System Administration > Entity Store > Refresh all cubes/measures. In that form you can setup update recurrence(some cubes you can update once a day, some once an hour). To access data in Tier2 you can use Direct Query. Direct Query connection to the cubes (AXDW database) is done through  Azure SQL database

Note: ODATA has access only to AXDB and does not have access to cubes, only to AXDB data.

• Tier1 environments have only SQL databases and that is real headache to configure PowerBI embedded for onebox environment. It requires external AXDW configured in Azure. 

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.