PowerBI: Compare different Timespans and Products in one Matrix

A recent requirement at a customer was to compare Sales Quantity and Sales Totals of different time spans in one matrix. The customer is selling saisonal as well as all-time products. It should be possible to compare the sale of saisonal products from last season to the actual sales.

Sum of different time spans and product

Data Model

The original data contains 6 rows: The product, the corresponding collection, the sold quantity, the unit price, calculated total price (Qty * Price), date.

In order to make the Quantity and Totals available for a row-like visualization the original data was unpivoted. This created a new column called “Attribute” and another Column called “Value”. The unit (pcs, €) have been added to the attribute to make it more readable. The resulting data looks like the following in PowerBI:

Filter tables

To implement 2 independet filter options based on date and collection 4 independet tables have been added. A date table and a collection table for both filter options. The date tables can easily be generated by using the Calendar() function:

Filter1Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))
Filter2Date = CALENDAR(Date(2021,1,1), Date(2022,12,31))

The collection tables are generated by using the Distinct() function on the collection column in the sales data table. Each of the tables contain a distinct list of collections available for filtering.

 Filter1Collection = DISTINCT(Sales[Collection])
 Filter2Collection = DISTINCT(Sales[Collection])

Measures

Three measures have been create. Two measures that sum up the Value column in the sales data filtered on the selected values in the Filter1* table or Filter2 table:

M_Sum1 = CALCULATE(
    SUM(Sales[Wert]),
    FILTER(
        Sales,        
        Sales[Date] >= FIRSTDATE(Filter1Date[Date]) &&
        Sales[Date] <= LASTDATE(Filter1Date[Date]) &&
        Sales[Collection] in VALUES(Filter1Collection[Collection])
        )
    )
M_Sum2 = CALCULATE(
    SUM(Sales[Wert]),
    FILTER(
        Sales,        
        Sales[Date] >= FIRSTDATE(Filter2Date[Date]) &&
        Sales[Date] <= LASTDATE(Filter2Date[Date]) &&
        Sales[Collection] in VALUES(Filter2Collection[Collection])
        )
    )

The third measure calculates the difference between the other two measures:

M_Diff = [M_Sum2]-[M_Sum1]

Visuals

Finally the report contains 5 visuals. Two slicers for date and collection on the left, another two slicers for date and collection on the right, and a matrix in the middle. The matrix shows the Attribute column as row and shows M_Sum1, M_Sum2 and M_Diff as values.

For example comparing collection “Summer 2021” from 1. Jan. 2021 till 30. Sep. 2021 to the aggregated values of “Collection 2022” + “Basic Wear” from 1. Jan 2022 till 30. Sep. 2022.

Comparing quantity and totals from different time spans and products in one matrix

The slices on the left only affect the M_Sum1 measure which shows quantity and totals in the Filter1 column while the slicers on the right effect only M_Sum2 measure which shows the results in Filter2 column. The difference is calculate dynamically when adjusting the slicers.

Custom Scripts: Run X++ Code without downtime (Video)

Custom Scripts in Dynamics 365 Finance and Supply Chain Management enables you to upload and execute X++ Code without the need to deploy a new release. Custom Scripts serve the same purpose like X++ Jobs in AX 2012 and earlier versions e.g. data correction. This short video shows how to create such a custom script, upload and execute it in a Dynamics 365 FO instance:

Custom Scripts: Jobs are (almost) back with in 10.0.27

The Custom Scripts feature in Dynamics 365 Finance and Supply Chain Management allows you to upload and execute code snippets. Microsoft implemented some barriers because this feature is potential dangerous. You need two accounts, one for uploading the script and one for approval. The feature can be found in System Administration > Periodic Tasks > Database > Custom scripts.

Custom Scripts Feature in 10.0.27

Donate Crypto – Invest in Peace 💛💙

Ukraine is suffering under a brutal Russian aggression. Not everyone can fight a battle but everyone can help. Donate Bitcoin, Ethereum, Dodge etc. and help Ukraine to defend themselves.

https://donate.thedigital.gov.ua/

How to create PowerBI Scorecard based on D365FO data

PowerBI goals are used to keep track of targets like sales quantity, quality, etc. Goals can be linked to values from PowerBI reports. This video shows how to create a scorecard with goals based on Dynamics 365 Finance data.

Admin Provisioning Tool Error: The value’s length for key ‘password’ exceeds it’s limit of ‘128’

Microsoft has recently released the new VHD for Dynamics 365 Finance and Operations 10.0.24 to download from LCS. When you instantly try to execute the Admin Provisioning Tool and provide your domain user you will get an error.

The value’s length for key ‘password’ exceeds it’s limit of ‘128’

There are some steps required before you can assign it to your Domain:

  1. Go to https://portal.azure.com > Active Directory > App Registration and register a new App
Register a new application in Azure Active Directory
  1. Provide the One-Box URL as reponse address
  2. Copy the AppID to your clipboard
App Registration for Dynamics 365 FO 10.0.24 One-Box Environment
  1. Execute the “Generate Self-Signed Certificates” PowerShell script from the Desktop
  2. Provide the AppID from the App Registration
Generate Self-Signed Certificates

6. Execute the Admin Provisioning Tool and provide your Domain account address
7. Open https://usnconeboxax1aos.cloud.onebox.dynamics.com/ in Edge and login

Dynamics 365 Finance and Operations 10.0.24 One-Box Environment

Store Dynamics 365 F/SCM Attachments on SharePoint

Dynamics 365 Finance & Supply Chain allows you to store attachments in the database, on Azure BLOB and on SharePoint. See here how you can configure SharePoint as storage location and create a document type “Fact Sheet”:

Dynamics 365 Environment missing in PowerBI Dataverse Connector

Dataverse is the name of the Dynamics 365 Apps database (e.g. Sales, Service, Field Services, etc.). PowerBI desktop comes with a builtin Dataverse connector. However, if you have multiple environments not all of them may show up in the connector.

Dataverse environment missing in PowerBI connector
Dataverse environment missing in PowerBI connector

In order to make the environment visible in PowerBI Desktop Dataverse Connector you have to enable PowerBI Embedded in your environment.

  1. Open Power Apps Admin Center https://admin.powerplatform.microsoft.com/
  2. Select the missing environment
  3. Open Settings (gear icon on the top)
  4. Search for TDS and open the Datastream Endpoint Configuration page
  5. In the group Embedded enable embedded PowerBI visuals
Enable PowerBI embedded visuals to see the Dataverse environment in the PowerBI connector
Enable PowerBI embedded visuals to see the Dataverse environment in the PowerBI connector

Now when you open PowerBI Desktop and use the Dataverse Connector you should see the missing environment.

Missing Dynamics 365 Dataverse environment is now visible in PowerBI Dataverse connector
Missing Dynamics 365 Dataverse environment is now visible in PowerBI Dataverse connector

Proposal for an Artificial Intelligence Act by the EU Commission

The European Commission is working on a proposal of a regulation on Artificial Intelligence. This proposal is origins from many initiatives and statements regarding the digital market and impact of AI on ethics and law. The goal is to create an EU wide effective law which protects rights and values by categorizing, forbidding and or requiring certain standards in Artificial Intelligence applications.

The proposal has a very wide idea on what AI is and what it wants to regulate. It aims on software based on techniques like machine learning, statistics and especially Bayse’ statistics, logic programming, inference and expert systems.

Category 1 : Forbidden

Some Artificial Intelligence applications which contradict rights and values of the European Union and it citizens shall be forbidden. The proposal names the following

  • Social Scoring by government agencies, i.e. scoring the social behavior of persons and creating a negativ impact. Such systems are known from China where non-conforming behavior can result in disadvantages when applying for a living space, university, etc.
  • Manipulate persons through subliminal techniques beyond their consciousness. A phenomenon that was recently shown in US elections where voters have been manipulated by using social media.
  • Remote Biometric Surveillance shall be banned but with very generous exceptions. For example searching for missing children, victims of crime and terrorists.

Category 2: High-Risk

Many Artificial Intelligence applications are considered to put rights of individuals on high risk and require therefore special legal treatment. The proposal lists a wide rang of possible high-risk applications. For example critical infrastructure, education, private and public services, law enforcement, migration, biometric identification.

The proposal states that for these applications require additional precautions like risk management, data quality, documentation, human supervision, hardening against security threats. It is also planned that high-risk applications have to be registered in a database before put to use.

Category 3: Low-Risk

Users of low-risk application needs to be informed that they are interacting with an AI application. For example using a chat bot. AI generated audio and videos also require labeling to prevent deep fakes. Games won’t need such a labeling because it is obvious that they present artificial generated content.

Fines

The AI act proposal suggests very high fines for violating the rules. For example using forbidden AI systems may result in a fine up to 30 Million € or 6% of the worldwide yearly revenue. Violating requirements for high-risk applications may result in 20 Million € or 4% world wide yearly revenue. Wrong or misleading information on national agencies 10 Million € or 2 % of world wide yearly revenue.

Industry statements regarding AI act

Many companies working on AI technology like IBM and Microsoft have handed in their own statement on the act. Microsoft has provided a statement that puts the spotlight on some problems in practice including the following key points.

  • The AI act proposal does not fully cover the complex AI eco-system. There are providers of general purpose AI components like image processing, text analysis etc. In many cases these of-the-shelf components are included in new applications which are then sold to customers and customized by other companies. Which participant in the eco-system has to take care of the requirements defined for high and low risk applications?
  • Some requirements in the act are unnecessary complex and unrealistic. For example article 10.3 requires error-free and complete data sets. This is almost impossible when training natural language or image processing applications.
  • Exception for remote biometric identification should be restricted. The actual proposal lists remote biometric identification as forbidden but gives law-enforcement a wide set of exceptions. For example searching for missing children, searching for victims, looking for terrorists and prevention of crimes. Microsoft states that the application of real-time biometric identification should only be used for serious and violent crimes. Moreover transparency reports should be mandatory for law-enforcement and judical authorities including what techniques were used, which training and test datasets were used, where, when, why and how long was remote biometric identification put to use.

The summary on the artificial intelligence act was created as part of the course “Algorithm, Discrimination and Law” at JKU Linz Winter term 2021.

Migrate Azure Analysis Services to another Tenant

Azure Analysis Services are SSAS as a Service hosted and managed in Azure. We recently had to migrate an analysis model from one tenant to another. Here is a video that illustrates how this can be done:

Find more videos in my Youtube Channel.