Creating a Matrix with Row and Column headers based on dynamic date calculation

[rcopenhagen]

Hi all,

I am attempting to make a matrix which would allow a user to select two dates and it would show the annual recurring revenue (ARR) bucket for the first date in the rows and for the second date in the columns. The values would be the count of unique account records.

I have two relevant tables:

Accounts

Account ARR by Date (this table has all dates for all accounts with a calculation for ARR based on the opportunity table for each account on each day)

I would then generate two calendars, each to be used in a slicer to select the dates which I would like to use to determine the ARR bucket for the columns and rows.

Result: 

		Date2
		0	<$10k	$10k-$100k	>$100k
Date1	0	16	2	5
	<$10k	2	12	4	1
	$10k-$100k	3	4	16	3
	>$100k	1		3	15

I was hoping to use a calculated column on the accounts table to leverage SELECTEDVALUE for the calendar, but it doesn't look like this is posisble with PowerBI, so I am hoping for an alternative.

The reason for using a matrix with the count of records in the values is so that a user can drilldown to see the particular accounts in any given change group.

Any ideas?

Thanks,

Rich

[MattAllington]

You can’t use calculates columns because they don’t recalculate based on slicers. My hunch is you will need to use a technique like I show here => Measures on rows in Power BI.  You could create 2 disconnected tables, one to feed columns and one to feed rows. You would then have to harvest the values from the intersection of row and column to do a calculation on a measure.