Highlighting a change in a table

[LordSnow]

Hello everyone, 

I have 12 months of data in 12 different data sheets. I have appended them into one datasheet. I would like to compare them in 2 tables. Both tables are connected to individual slicers that select the month. Now I would like to see if any entry was changed in the current month compared to last month. To simplify, let's say Jim placed an order in the month of June, which was to be delivered in August. However, Jim canceled his order in July. There are 1000s of entries every month. and 100s of them get canceled the following month. I would like to Implement a DAX such that Jim's entry in June is highlighted. 

If anyone could help me with this issue, I would be glad. Thank you

[123abc]

To highlight changes in your table based on the current month compared to the previous month, you can create a DAX measure that identifies whether a record was canceled or changed. You can then apply conditional formatting to your table using this measure. Here's a step-by-step guide:

1. Create a New DAX Measure:

   In Power BI, go to the "Model" or "Data" view and create a new DAX measure. You can name it something like "Change Indicator."

Change Indicator =
VAR CurrentMonth = SELECTEDVALUE(Calendar[Month])
VAR PreviousMonth = CurrentMonth - 1
RETURN
IF(
ISINSCOPE('YourTableName'[Name]), // Replace 'YourTableName' with your actual table name
IF(
COUNTROWS(
FILTER(
ALL('YourTableName'),
'YourTableName'[Month] = CurrentMonth &&
'YourTableName'[Status] = "Canceled"
)
) > 0,
"Canceled",
IF(
COUNTROWS(
FILTER(
ALL('YourTableName'),
'YourTableName'[Month] = PreviousMonth &&
'YourTableName'[Name] = VALUES('YourTableName'[Name]) &&
'YourTableName'[Status] = "Canceled"
)
) > 0,
"Changed",
BLANK()
)
),
BLANK()
)

1. Replace 'YourTableName' with the actual name of your table, and adjust the column names ([Month], [Name], [Status]) as needed to match your data structure.

   This DAX measure checks if a record is "Canceled" in the current month, and if not, it checks if the same record was "Canceled" in the previous month. If either condition is met, it marks the record as "Canceled" or "Changed."

2. Apply Conditional Formatting:

   Now, go to your table visual where you want to highlight the changes.

   • Select the column (e.g., the "Name" column) where you want to apply the conditional formatting.

   • In the "Visualizations" pane, under "Format," expand the "Conditional formatting" section.

   • Choose "Background color."

   • Select "Color by rules."

   • Add a new rule:

     • Format by: "Field value"

     • Summarization: "None"

     • Select your "Change Indicator" measure from the list.

     • Set the background color for the "Canceled" or "Changed" entries to your desired highlighting color.

3. Apply Slicers:

   Ensure that your slicers for selecting the month are connected to both tables (the current month and the previous month) so that you can dynamically change the comparison period.

With these steps, your table should now highlight entries that have been canceled or changed in the current month compared to the previous month based on the selected slicer month.

[LordSnow]

it does not work.

[123abc]

I apologize for any confusion. To highlight changes in Power BI, you can use conditional formatting with a measure that compares the current month's data with the previous month's data. Let's break it down into simpler steps:

1. Create a Date Table: Ensure you have a Date table with a relationship to your data table. This Date table should have a continuous sequence of dates covering your entire data range.

2. Calculate the Previous Month: Create a DAX measure that calculates the previous month. You can use the following DAX expression to do this:

Previous Month = MAX('Date'[Date]) - 1

Create a Measure to Identify Changes: Now, create a measure that compares the current month's data with the previous month's data. Assuming your data table is called "Orders," and you want to compare the "Order Status" column, you can use this DAX measure:

Status Change =
VAR CurrentMonth = MAX('Date'[Date])
VAR PreviousMonth = [Previous Month]

RETURN
IF(
COUNTROWS(
FILTER(Orders, Orders[Order Month] = CurrentMonth)
) > 0
&&
COUNTROWS(
FILTER(Orders, Orders[Order Month] = PreviousMonth)
) > 0
&&
CALCULATE(
COUNTROWS(Orders),
FILTER(Orders, Orders[Order Month] = CurrentMonth && Orders[Order Status] = "Canceled")
) >
CALCULATE(
COUNTROWS(Orders),
FILTER(Orders, Orders[Order Month] = PreviousMonth && Orders[Order Status] = "Canceled")
),
"Changed",
"Not Changed"
)

1. Apply Conditional Formatting: Now, go to your table visual, select the "Order Status" column, and apply conditional formatting. Choose "Background color" or "Font color," and set the formatting rule based on the "Status Change" measure. You can specify a color for "Changed" and leave the default for "Not Changed."

With these steps, your table will highlight entries where the status changed from the previous month to the current month. Ensure that your data model is set up correctly, and the column and table names match your actual data.