Calculated Measure that compares a date from one table to a date from another joined table

[thx1137]

All,

I have a measure that is a distinct count of customers.  We snapshot our data every month and we are always interested in knowing how many new customers we win each month and we track which quarter they first became customers so that we can understand how many we win over time.

The standard visual used to show this is a matrix, where the snapshot months (represented by the date of the last day of the month) are the columns and the quarters of the customer wins (represented by the date of the last day of the quarter) are the rows.

The calculated measure works fine in this visual as it has both dates as context so that each "cell" of the matrix shows how many customers were "net new" in that particular intersection.

The challenge is when we want to put a category other than win quarter on the rows (e.g. Customer Region) as that Win Quarter context is gone.

We have attempted to put that context into the measure filter (customer win quarter date = ENDOFQUARTER(snapshot date) however it doesn't return the desired result.  Both fields leveraged in this filter are identified in our data set as Dates and we don't get any errors.

Any thoughts?

[thx1137]

Thanks all - turns out I had made an error in the code, resolved thru debugging with DAX Studio.

[v-shex-msft]

HI @thx1137,

What type of error message are you faced? Can you please share some more detail information about this issue? They should help us clarify your scenario and test to troubleshoot.

Regards,

Xiaoxin Sheng

[amitchandak]

@thx1137 , You can try like

This Qtr  =
var _max1 = max(date[Date])
var _min = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,mod(Month(_max1),3)))+1
var _max= eomonth(_min, 2)
return
calculate(sum(Table[Value]), filter(Date, Date[Date] = _max) )

or

This Qtr  =
var _max1 = max(date[Date])
var _min = eomonth(_max1,-1* if( mod(Month(_max1),3) =0,3,mod(Month(_max1),3)))+1
var _max= eomonth(_min, 2)
return
calculate(sum(Table[Value]), filter(all(Date), Date[Date] = _max) )

[v-shex-msft]

Hi @thx1137 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng