Matrix to show data for last two months with a caveat

[RogerSteinberg]

Hi all,

Currently my matrix visual looks like this:

Current: Previous two months sales

My problem is that i don't know how to hide the "diff" and "%" data of february. Assume we are in april and we are always excluding current month.

I would rather my result to look like this:

Hence, I have a visual level filter: relative date filtering showing items when the value is in the last 2 calendar months.

My measures are pretty simple:

sales = AVERAGEX(table, table[column])
diff = 
VAR previous_sales =
CALCULATE(sales, PREVIOUSMONTH('Date'[Date])
RETURN
sales - previous_sales
diff % = DIVIDE(sales-previous_sales,previous_sales)

[parry2k]

@RogerSteinberg may be you want to change to table visual and use following to break down your dax and use following measures in your visual

- sales

- prev sales

- diff%

I believe it will have cleaner look.

sales = AVERAGEX(table, table[column])

previous_sales =
CALCULATE(sales, PREVIOUSMONTH('Date'[Date])

diff = 
sales - previous_sales

diff % = DIVIDE(diff,previous_sales)

[RogerSteinberg]

Any way to keep the matrix visual ?

[parry2k · Accepted solution]

@RogerSteinberg you can try following approach

Add "Month offset" column in your date table

Month Offset = DATEDIFF (TODAY(), DateMaster[DateID], MONTH )

And update your "diff %" measure like this

Diff % = 
IF( MAX( Date[Month Offset] = 0, <your diff % calculation> )

Basically it is going to check if month offset is not 0 then diff % is blank.

[RogerSteinberg]

@parry2k It worked well however i had a visual level filter which only takes the last two calendar months.

I've modified the diff formula to equal to -1 instead of 0.

My issue now is that all the dates are being shown (two years).

My visual level filter is not taken into account anymore. How do i modify the diff formula to only show the last two calendar months.

[parry2k]

@RogerSteinberg sorry not fully sure what you mean, if you can share some image to explain, it will help.

[RogerSteinberg]

@parry2k Basically I only want to show the data for the past two calendar months. So i added a relative visual level filter.

For example, we are in april. So the matrix will show february and march.

When i added your formula, it added every month since 2016 and the visual level filter doesnt work anymore.

[parry2k]

@RogerSteinberg hmmm, can you share how the output looks like? Not sure why it is showing all the months.

[RogerSteinberg]

[parry2k]

@RogerSteinberg interesting, can you double check "Month Offset" is getting calculated correctly. It should be unique for each month. I will double check at my end thou.

[RogerSteinberg]

@parry2k  . Month offset is fine. Its -1 for all of the month of march, -2 for february.
basically i only want to show data when its -1 and -2

[parry2k]

@RogerSteinberg I checked and not able to produce the issue at my end, everything is working as expected. Can you share pbix file (may be remove any sensitive information it has). You can also send me PM.

[RogerSteinberg]

@parry2k It worked well however i had a visual level filter which only takes the last two calendar months.

I've modified the diff formula to equal to -1 instead of 0.

My issue now is that all the dates are being shown (two years).

My visual level filter is not taken into account.