Calculating difference between columns

BigMikey 

Transitioning from Tableau to Power BI can definitely require some adjustments, especially when you're used to Tableau's straightforward handling of table calculations. In Power BI, you can achieve similar results with DAX (Data Analysis Expressions), which are used to create calculations in your data model. To add a month-over-month change column in your matrix, you will need to create a measure that calculates this change. Here’s how you can do it:

### Step 1: Create a Date Table

First, ensure that you have a date table. If you don't already have one, you can create one in DAX. This is important for handling time intelligence functions properly.

```DAX
Date Table =
CALENDARAUTO()
```

Connect this date table to your main data table (`your_data_table_name`) by matching date columns (e.g., the `Month` column in your main table should correspond to the `Date` column in your Date table, though you may need to ensure the `Month` column is in a proper date format).

### Step 2: Create a Measure for Total Visitors

It sounds like you might already have this, but if not, here’s a simple measure to sum visitors:

```DAX
Total Visitors = SUM('your_data_table_name'[Visitors])
```

### Step 3: Create the Month-over-Month Change Measure

Now, create a measure to calculate the month-over-month change. This measure will use the `Total Visitors` measure and the DAX function `DATEADD` to compare the current period with the previous one.

```DAX
MoM Change =
VAR CurrentMonthVisitors = [Total Visitors]
VAR PreviousMonthVisitors = CALCULATE([Total Visitors], DATEADD('Date Table'[Date], -1, MONTH))
RETURN
CurrentMonthVisitors - PreviousMonthVisitors
```

This measure calculates the total visitors for the current month and the previous month, then finds the difference.

### Step 4: Add the Measure to Your Matrix

- Drag this new measure into the Values area of your matrix visual alongside the total visitors.
- Ensure that your matrix is using the Date column from your Date table for the column headers to correctly align the data by month.

### Testing and Adjustments

Make sure to test the calculation to ensure it's behaving as expected. You may need to adjust how the date relationships are set up, or how the date data is formatted and calculated depending on the specifics of your data and model.

### Additional Tips

- **Formatting Dates**: If your month data isn't in date format, you might need to convert it using DAX to ensure that time intelligence functions work properly.
- **Handling Year Crosses**: Be careful with date calculations around the end/start of a year; DAX handles these well as long as your date table is complete and properly connected.
- **Check Model Relationships**: The effectiveness of DAX calculations heavily depends on correct model relationships. Ensure your Date table is correctly related to your main data table.

This approach should give you the month-over-month visitor change calculation you’re looking for in your Power BI matrix. If you encounter any further issues, feel free to ask more questions!

Hi all,

First of all, thanks for the positive responses!  Then wanted to share my method.

Hi BigMikey ,

I have create a simple sample, please reer to my pbix file to see if it helps you.

Measure = var _1=CALCULATE(SUM('Table'[  Visitors]),FILTER(ALL('Table'),'Table'[  Month]=SELECTEDVALUE('Table'[  Month])))
return 
_1

Measure 2 = var _1=EDATE(MAX('Table'[  Month]),-1)
return
CALCULATE([Measure],FILTER(ALL('Table'),'Table'[  Month]=_1))

Measure 3 = var _1=[Measure]-[Measure 2]
return _1

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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