Matrix Visual, Find Difference Between 2 Columns, Dynamic Data Filtered by Last 2 Calendar Months

[mangchaaBI]

Hi All,

It's me again, just need some help in getting the difference between 2 month columns in my report using Matrix visualization

The months are automatically filtered using Last 2 Calendar Months

Thanks as always!

[v-yadongf-msft]

Hi @mangchaaBI ,

I think you'd better use a table visual.

This is my test table:

Please try following DAX to create three new columns:

Last 2 month_August = CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date])=8 && 'Table'[ItemName]=EARLIER('Table'[ItemName])))

Last 1 month_September = CALCULATE(SUM('Table'[Value]),FILTER('Table',MONTH('Table'[Date])=9 && 'Table'[ItemName]=EARLIER('Table'[ItemName])))

Difference = [Last 1 month_September] - [Last 2 month_August]

Create a table visual:

Best regards,

Yadong Fang

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

[Anon2020]

And what if I need the years to be dynamically selected by the user instead of manually input?

[hnguy71 · Accepted solution]

@mangchaaBI ,

In this case, you would need a minimum of three measures. I'm assuming you already have two measures created. I am hoping my assumption is correct. If not you'll need to create those measures.

1 for the sum of the last 2 months

1 for the sum of the last 1 month

You would then take the sum of both and subtract the other. In your case the formula would be..

Delta := [Last1Month] - [Last2Month]

[mangchaaBI]

Hi @hnguy71  I don't have the measures created yet, but I think I'm getting your instructions, can take it from here, thanks!