Temporal Line Chart Filter Problems

[Antonio_Carneir]

Hello!

Recently, I was assigned a task to develop a line chart in Power BI that would show the price variation history of certain products. For this purpose, I was provided with a database with the following columns:

SKU = Product Name
Extraction Date = Date
UF = Country State
Value = Price of a certain product, in a certain State (UF), on a certain date (Extraction Date). In addition, a calendar table was also imported.

Given that prices do not change every day, I created a series of columns and measures whose objective is to indicate whether there was a price variation on a given day. The logic is as follows:

Price_Change? = IF(db_preco_industria[Value]-db_preco_industria[Value Previous Day]=0, “No”, “Yes”)

This way, it was possible to filter the database only for the days when there was a price variation in a certain SKU.

When trying to build the chart, I created two measures [Price] and [Price2] whose formulas are presented below:

Price = AVERAGE(db_preco_industria[Value])

Price2 = VAR Current_Days = FILTER(KEEPFILTERS(VALUES(‘Calendar (English names)’[Date])), NOT ISBLANK(CALCULATE([Price],db_preco_industria[Price_Change?] = “Yes”)))

VAR Result = CALCULATE([Price],KEEPFILTERS(Current_Days)) RETURN Result

When trying to build the chart, I chose the desired visual and applied the ‘Extraction Date’ field on the X-axis, ‘Price_2’ on the Y-axis, and the SKU name as legends. However, the final result was not as expected and is shown in the figure below. Those lines correspond to the pice historic of two distincts SKU's

Ideally, the display would be correct if those blue lines were completely connected, but something is preventing that from happening. Can someone help me with a solution?

Regards, Antônio

[123abc]

Let's try to address the issue step by step:

1. Data Model & Relationships: Ensure that there is a relationship established between your calendar table (which seems to be named 'Calendar (English names)') and your main data table (db_preco_industria) on the 'Extraction Date' field. This ensures that time-based filtering works correctly.

2. Refining the Price2 Measure: The measure you've created seems a bit complicated. Let's simplify it to get a clearer result. Instead of creating a variable and applying multiple filters, we can use the FILTER and SUMMARIZE functions to ensure we're getting the correct dates for which there's a price change.

Here's a refined version of your Price2 measure:

Price2 =
VAR ChangedDays =
FILTER(
SUMMARIZE(
db_preco_industria,
'Calendar (English names)'[Date],
"HasChange",
MAX(db_preco_industria[Price_Change?])
),
[HasChange] = "Yes"
)
RETURN
CALCULATE(
AVERAGE(db_preco_industria[Value]),
ChangedDays
)

In this measure, we first create a summarized table to identify dates where there's been a price change for any SKU. Then, we use this summarized table to filter the average price calculation.

1. Visualization: After refining the measure, drag the 'Extraction Date' field to the X-axis, 'Price2' measure to the Y-axis, and use the SKU name as a legend. Ensure that the chart type is set to 'Line Chart'.

2. Date Filters: In Power BI, sometimes, the issue might arise due to the default date hierarchy. Ensure that when you're applying the 'Extraction Date' to the axis, you're not inadvertently using a hierarchy (like Year-Month-Day). Instead, you should just be using the 'Date' field without any hierarchy.

3. Data Completeness: Double-check your data to ensure that for the dates in question, there are no missing values or anomalies that could cause breaks in your line chart.

After making these adjustments, try to visualize the chart again. If the issue persists, you might want to inspect the underlying data for any anomalies or inconsistencies that might be causing the disconnect in the lines.

I hope this helps you resolve the issue. Let me know if you need further assistance!

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[Antonio_Carneir]

Thanks for the reply!

I made all the adjustments and checks as you specified, but apparently there was no change in the result reflected in the charts. The databases are “OK”, so it seems to me that the problem here is due to the fact that, somehow, the "HasChanged" filter is also affecting the Value display.