Getting count of filtered transaction from same Nth wwkday last month

[Anonymous]

I have a FactTable of individual transactions by date and by person and by Category 1 and Category 2.

Person	Date	Category1	Category2	Current Nth Weekday
John	7/1/2020	A	C	1st Wed of July
John	7/1/2020	B	C	1st Wed of July
John	7/7/2020	A	D	1st Tue of July
John	7/7/2020	B	D	1st Tue of July
John	7/8/2020	A	C	2nd Wed of July
John	8/3/2020	A	D	1st Mon of Aug
John	8/4/2020	A	C	1st Tues of Aug
John	8/5/2020	A	C	1st Wed of Aug

Also have a formal CalendarTable which I’ve computed and poplulated a column for “nth Weekday as Previous Month”.  There is a relationship between CalendarTable[Date] and FactTable[Date].

Date		Nth Weekday Previous Month
6/2/2020	1st Tue	(ignore)
6/3/2020	1st Wed	(ignore)
6/10/2020	2nd Wed	(ignore)
7/1/2020	1st Wed	6/3/2020
7/6/2020	1st Mon	6/1/2020
7/7/2020	1st Tues	6/2/2020
8/3/2020	1st Mon	7/6/2020
8/4/2020	1st Tue	7/7/2020
8/5/2020	1st Wed	7/1/2020

On a Power BI report page where all visualizations have Person, Date, Category1 and Category2 influenced by slicers, I currently do MeasureA = COUNTROWS(FactTable) to get transaction count.

Assuming slicers are Select All, I correctly get:

Person	July	August
John	5	3

However, I additionally need to show, on same page, the number of transactions of the same Nth Weekday from the previous month.  If my slicers are Select All, I would expect:

Person	July	August
John
This Month	5	3
Nth Weekday Previous Month		4

The reasoning of my expected results is:

- John has no FactTable transactions for June therefore “Nth Weekday Previous Month” in July is blank.

- John has the following transactions for July that match his transactional dates for August:

Person	Date	Category1	Category 2		Matching Nth Weekday in Jul	Category1	Category 2
John	8/3/2020	A	D	1st Mon of Aug >> 1st Mon of Jul	blank
John	8/4/2020	A	C	1st Tues of Aug >> 1st Tues of Jul	7/7/2020 7/2/2020	A B	D D
John	8/5/2020	A	C	1st Wed of Aug >> 1st Wed of July	7/1/2020 7/1/2020	A B	C D

Aug:          Count = 3                                                                                             Count = 4                                                                                                        

The result needs to also respect the slicers on the different Categories.  So, if Slicer for Category1 = A and Slicer for Category2 = C, I would expect:

Person	July	August
John
This Month	2	2
Nth Weekday Previous Month		1

How do I achieve this? 

Thanks in advance for your help.

[lbendlin]

You're nearly there.  Keep in mind that you need to keep your original date for the visual. That means you need to create a measure (or in a pinch even a calculated column, as it's a fixed difference) that takes the current date context and then calculates the value by overriding the date filter with the date from your helper column.

[Anonymous]

I think I understand what you're saying.  I've been trying all kinds of different Measures for the past couple days.   I thinking I'm stumbling on the "...overriding the date filter with the date from my helper..."  How do I that?

[lbendlin]

Maybe you can post your sample data - that makes it easier to design a solution.

[Anonymous]

I'll work on building a sample and sharing it.