Displaying the most recent values in Power BI

[JanPBI29]

Hello guys, 

I have an issue with a power bi report I am working with and I am not able to find a solution by myself. I hope that someone might be able to help me with this. 🙂 

I work with data collected by our field service. Our field service visits our customers several times a year and records each time which items from our product range were present during the visit and which were not. With my DAX formula, I want to calculate the number of items that were not present during the last customer visit per customer.

This results in the following issue: it can happen during the year that products are discontinued and no longer sold. Consequently, these products cannot be recorded by the field staff during subsequent visits. If there is a customer visit after an item has been discontinued, that item should no longer be displayed for the customer.

However, when I add the item dimension, for example in a bar chart, Power BI does not show me the number of missing items from the last customer visits; instead, it shows me the last visit where a particular item was still recorded, even if that does not correspond to the last visit with that customer.

The goal is for the formula to consider exclusively the data from the last customer visit, even when the items are included in the chart. It is important that the formula dynamically adjusts to the date filter. This means that data from the most recent visit should always be displayed, depending on the applied date filter.

Here is my current formula:

01_Distributionsanalyse sortb. Quote in Anzahl Artikeln = 

VAR AktuelleDistributionID =

    CALCULATE(

        MAX('Distributions'[Distribution ID]),

         FILTER(

            'Distributions',

            'Distributions'[Datum] =

            CALCULATE(

                MAX('Distributions'[Datum]),

                Filter(

                    'Distributions',

                    'Distributions'[Datum] <= MAX(('Termine'[tatsächlicher Start])

    )))))

VAR GelisteteArtikel =

    CALCULATE(

        COUNT('Distribution Detail'[Product ID]),

        'Distribution Detail'[Distribution ID] = AktuelleDistributionID

    )

VAR DistribuierteArtikel =

    CALCULATE(

        COUNTROWS(

            FILTER(

                'Distribution Detail',

                'Distribution Detail'[Distribution ID] = AktuelleDistributionID &&

                'Distribution Detail'[Verteilt] = TRUE()

            )

        )

    )

VAR NichtGelisteteArtikel =

    110 - GelisteteArtikel

VAR AnzahlArtikelzuVolldistribution =

    110- NichtGelisteteArtikel - DistribuierteArtikel

VAR LetzterBesuch =

    CALCULATE(

        MAX('Termine'[tatsächlicher Start]),

        FILTER(

            'Termine',

            NOT(ISBLANK('Termine'[tatsächlicher Start]))

        )

    )

RETURN

    IF(

        NOT(ISBLANK(LetzterBesuch)) && GelisteteArtikel > 0,  

        BLANK()  

    )

To sum the result of the formula I use a second Dax Formula: 

01_Distributionsanalyse sortb. Quote in Anzahl Artikel aggregiert = 

SUMX(

    VALUES('CRM Kunden'[Name]),

    [01_Distributionsanalyse sortb. Quote in Anzahl Artikeln]

)

When you display the data in a table visual without the specific products it shows the correct value, e.g. 

Customer	Number of missing Articles	Last Visit
Customer 1	2	01.08.2024
Customer 2	4	30.06.2024
Customer 3	1	01.09.2024
Customer 4	5	12.07.2024

 When you Display the data with the product dimension added, then the issue begins: 

This bar chart should display the number of missing articles added from all the latest visits per customer. Instead it displays the data from the last visit where the specific product was being recorded. 

As there a quite a few tables in the dataset, here is a Screenshot of the most important tables for this calculation: 

CRM Kunden = Contains the Name and ID for each specific customer

Systemuser = Contains name and ID for each specific field service worker

Distributions = Contains ID and date of the distribution (where the products are being recorded)

Distribution Detail = Contains the details for each specific distribution (Which products were present during the customer visit and which were not. 

It would be amazing if someone could help me to find a solution with this, as I have tried for weeks now without success. 

Best regards, 

Jan 

[Poojara_D12]

Hi @JanPBI29 

The issue lies in the way Power BI evaluates your DAX measures in conjunction with the visual context. When you add the product dimension to the visual, it changes the row context, and the measure now evaluates based on the last visit where a specific product was recorded, rather than focusing on the most recent visit per customer.

To resolve this issue, you need to ensure that your DAX measure calculates the number of missing articles strictly based on the most recent customer visit, independent of the product context.

Revised DAX Formula:

Here is a refined approach:

1. Determine the Most Recent Visit Per Customer: Create a measure to dynamically identify the latest visit date per customer:

Last Visit Date = 
CALCULATE(
    MAX('Distributions'[Datum]),
    FILTER(
        'Distributions',
        'Distributions'[Datum] <= TODAY()
    )
)

• Filter by the Most Recent Visit: Modify your formula to calculate missing articles based only on the most recent visit date:

Missing Articles (Latest Visit) = 
VAR AktuelleDistributionID =
    CALCULATE(
        MAX('Distributions'[Distribution ID]),
        FILTER(
            'Distributions',
            'Distributions'[Datum] = [Last Visit Date]
        )
    )
VAR GelisteteArtikel =
    CALCULATE(
        COUNT('Distribution Detail'[Product ID]),
        'Distribution Detail'[Distribution ID] = AktuelleDistributionID
    )
VAR DistribuierteArtikel =
    CALCULATE(
        COUNTROWS(
            FILTER(
                'Distribution Detail',
                'Distribution Detail'[Distribution ID] = AktuelleDistributionID &&
                'Distribution Detail'[Verteilt] = TRUE()
            )
        )
    )
VAR NichtGelisteteArtikel =
    110 - GelisteteArtikel
VAR AnzahlArtikelzuVolldistribution =
    110 - NichtGelisteteArtikel - DistribuierteArtikel
RETURN
    IF(
        GelisteteArtikel > 0,
        AnzahlArtikelzuVolldistribution,
        BLANK()
    )​

• Adjust for Product Context: Use REMOVEFILTERS to ignore the product filter applied by the visual. Wrap the key parts of your measure in CALCULATE and REMOVEFILTERS:

Missing Articles (Latest Visit, Adjusted) = 
CALCULATE(
    [Missing Articles (Latest Visit)],
    REMOVEFILTERS('Distribution Detail'[Product ID])
)

This ensures that the calculation remains focused on the latest customer visit and is not affected by the product dimension.

Testing and Debugging:

1. Visual Without Product Context: Add a table visual to verify that the calculated number of missing articles matches the expected result for each customer.

2. Visual With Product Context: Add the product dimension to the visual to ensure the number of missing articles is constant and not split by individual products.

3. Validation:

   • Ensure that 'Distributions'[Datum] has continuous and valid dates.
   • Validate that 'Distribution Detail'[Distribution ID] correctly links to 'Distributions'[Distribution ID].

---

Explanation:

• By explicitly filtering for the last visit per customer (Last Visit Date) and removing the product filter using REMOVEFILTERS, the measure consistently calculates missing articles at the customer level.
• The use of CALCULATE ensures that the filtering logic works regardless of visual context.

Let me know if you need further clarification or if there are additional nuances in your dataset!

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS

[JanPBI29]

Hello, @Poojara_D12 

thank you for your Quick Response. This is the result the DAX Expression provides: (not showing the articles names)

The formula seems to display only the values of the latest visit in the selected time range.

What I need is the sum of missing articles per customer only considering the latest visit per customer. 

Best regards, 
Jan 

[DataNinja777]

Hi @JanPBI29 ,

To address the issue where the bar chart incorrectly reflects the last visit where a specific product was recorded instead of the latest customer visit, the key is to isolate the context for the last customer visit dynamically while ignoring the item dimension in visualizations. Here's how you can refine your approach:

Key Adjustments:

1. Focus on Last Customer Visit:
• Ensure the last visit for each customer is determined dynamically based on the Date filter.
2. Exclude Discontinued Items:
• Dynamically filter out discontinued items for visits after their discontinuation.
3. Ignore Item Context:
• Use DAX functions to overwrite the item's filter context when calculating missing items.

Number of Missing Items = 
VAR LastVisitDate = 
    CALCULATE(
        MAX('Distributions'[Datum]),
        FILTER(
            ALL('Distributions'), 
            'Distributions'[Customer ID] = SELECTEDVALUE('CRM Kunden'[Customer ID])
        )
    )

VAR ValidProducts = 
    CALCULATETABLE(
        'Distribution Detail',
        FILTER(
            ALL('Distribution Detail'),
            'Distribution Detail'[Discontinued] = FALSE() || 
            'Distribution Detail'[Discontinued Date] > LastVisitDate
        )
    )

VAR MissingItemsCount = 
    CALCULATE(
        COUNTROWS(ValidProducts),
        FILTER(
            ValidProducts,
            'Distribution Detail'[Present] = FALSE() &&
            'Distribution Detail'[Distribution Date] = LastVisitDate
        )
    )

RETURN
    IF(ISBLANK(LastVisitDate), BLANK(), MissingItemsCount)

When adding the product dimension, the values should still aggregate correctly by the last visit date and not by the last date where a specific product was recorded.

Best regards,

[JanPBI29]

Hello, @DataNinja777 

thank you for your fast reply!

I filled the columns of your formula with the column names of my specific data set: 

VAR LastVisitDate =

    CALCULATE(

        MAX('Distributions'[Datum]),

        FILTER(

            ALL('Distributions'),

            'Distributions'[Account ID] = SELECTEDVALUE('CRM Kunden'[accountid])

        )

    )

VAR ValidProducts =

    CALCULATETABLE(

        'Distribution Detail',

        FILTER(

            ALL('Distribution Detail'),

            'Distribution Detail'[Verteilt] = FALSE() ||

            'Distributions'[Datum] > LastVisitDate

        )

    )

VAR MissingItemsCount =

    CALCULATE(

        COUNTROWS(ValidProducts),

        FILTER(

            ValidProducts,

            'Distribution Detail'[Verteilt] = FALSE() &&

            'Distributions'[Datum] = LastVisitDate

        )

    )

RETURN

    IF(ISBLANK(LastVisitDate), BLANK(), MissingItemsCount)

Here is some additional information: 

The date of distribution is stored in the Distributions table, not in the Distribution Details table. If this column is inserted in that place, the following error message is displayed:

A single value for the column "Datum" in the table "Distributions" cannot be determined. This can occur when a measure formula refers to a column with many values, without specifying an aggregation such as MIN, MAX, COUNT, or SUM to produce a single result.

Additionally, it is important to note that data from multiple field staff flows into the Distributions table, which means that certain date values may occur more than once. Moreover, there is no specific "Discontinued date." There is only the distribution date. Whether a particular item was recorded or not is reflected in the Distribution Detail table, in the column "Verteilt," with a value of True or False.

Regarding the date I have created a date table (Datum_Retail) that only contains specific date values. The date Table is connected to the "Termine" Table (english: Appointments). That table is then connected to the table where the information about the customers is stored ("CRM Kunden"). 

I am not sure if all this information is relevant for solving the problem but I hope that it helps to get a better understanding for how the dataset works. Due to privacy reasons of my company I am not allowed to share the pbix file. Otherwise I would have already uploaded the file. 

I hope this additional information can help to solve the problem. 

Thank you very much! 

Best regards, 

Jan 

[DataNinja777]

Hi @JanPBI29 ,

To address the issue with your dataset and formula, the key is to resolve the ambiguity around the Datum column and handle relationships between the Distributions and Distribution Detail tables effectively. The error occurs because the formula references the Datum column in Distributions without clearly defining how it relates to Distribution Detail. Additionally, since data from multiple field staff is included, the formula must dynamically isolate the relevant records for the last customer visit. Without a Discontinued Date, the validity of products must be inferred based on the Verteilt column during the last visit.

Here’s a revised formula to account for these requirements:

Number of Missing Items = 
VAR LastVisitDate = 
    CALCULATE(
        MAX('Distributions'[Datum]),
        FILTER(
            ALL('Distributions'),
            'Distributions'[Account ID] = SELECTEDVALUE('CRM Kunden'[accountid])
        )
    )

VAR ValidProducts = 
    CALCULATETABLE(
        'Distribution Detail',
        FILTER(
            ALL('Distribution Detail'),
            'Distribution Detail'[Verteilt] = FALSE() && 
            RELATED('Distributions'[Datum]) = LastVisitDate
        )
    )

VAR MissingItemsCount = 
    COUNTROWS(ValidProducts)

RETURN
    IF(ISBLANK(LastVisitDate), BLANK(), MissingItemsCount)

This formula calculates the last visit date for each customer using the Datum column from the Distributions table. It then identifies products not marked as "distributed" during that visit by bridging the Distributions table with Distribution Detail through the RELATED function. The filtering logic ensures that only rows corresponding to the last visit are evaluated, isolating the customer-specific context dynamically.

With this approach, the formula should work for each customer while properly handling the relationships between tables. Ensure that the relationships between Distributions, Distribution Detail, and CRM Kunden are correctly defined in your data model to allow the RELATED function to access the necessary columns. Test the measure in different scenarios, such as adding or omitting the product dimension in your visualizations, to confirm that it behaves as expected.

Best regards,

[JanPBI29]

Unfortunately the table I wanted to create as an example looks quite bad: 

Second Try: 

Customer         Number of missing Articles      Date

Customer 1            2                                          01.08.24

Customer 2            4                                          30.06.24

Customer 3            1                                          01.09.24

Customer 4            5                                          12.07.24