Calculating difference between columns

[BigMikey]

Long time Tableau guy, trying out PowerBI. Struggling with something very basic. I have data that I'm displaying in a matrix. My source data looks like this:

State	Month	Visitors	Park
Delaware	March 2024	3	City Park
Delaware	March 2024	56	Forest Park
Delaware	February 2024	6	City Park
Delaware	February 2024	8	Forest Park

My matrix looks like this:

State	February 2024	March 2024
Delaware	14	59

I want to add an additional column that shows the month over month change. In tableau this was super easy with table calcs. What's the easiest way to replicate this in PowerBI?

[AnalyticsWizard · Accepted solution]

@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!

[v-rongtiep-msft · Accepted solution]

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.

[AnalyticsWizard · Accepted solution]

@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!

[Ramendra]

Hello,

There are options available in quick measure. You can choose the calculations as required.

[BigMikey]

I tried this and it breaks the visual when I add it. Do you happen to have a suggestion for a good overview on the use of this feature? Thank you!

[miTutorials]

Visual Calculations are part of Power BI too just make sure you are on the latest version of Power BI.

Check out the below video to get this working !

🚀 NEW FEATURE : Magic of Visual Calculations in Power BI | MiTutorials - YouTube

[BigMikey]

Thanks for the insight - This seems like a great feature. I'm probably doing something wrong because with my data, it seems to put a calculation field after each Month. I think this might be because the date is technically one column in my data. When I do this with two different columns displayed, it seems to work as I would expect.