Average Days between two dates for each calendar month

[Anonymous]

Hi All,
I have a requirement to create a column AVG Days Sale to Event which will evaluate for all events/performance sold in a calendar month, the average number of days between the date of sale and the date of the event/performance.

My data model looks like the below screenshot where I have Transaction and Performance table joined on Performance Key and I can fetch Transaction Date Key and Performance Date time as the date of sale and date of the event.

The challenge I face is how to restrict Performance Date Time to be the same calendar month. Like in the below-shown screenshot for Calendar Month Jun-20, Performance Date Time shows all the dates and I want it to calculate for the same month only.

Days Diff Sale to Event =

VAR TransactionDate = MAX('Transaction'[Transaction Date Key])

VAR EventDate = MAX('Performance'[Performance Date Time])

VAR DaysDiff = CALCULATE(DATEDIFF(TransactionDate,EventDate,DAY))

VAR CountMonthRows = COUNTROWS(CALCULATETABLE(VALUES('Performance'[Performance Name]),

DATESBETWEEN('Transaction Date'[Date Key],TransactionDate,EventDate)))

RETURN DaysDiff

Can you please help me with the DAX to calculate the Average Days Sale to the event for each calendar month (Transaction Date table - Month Year)?
Here is the sample data 
https://docs.google.com/spreadsheets/d/1xmg36d-68GnFWTj8cHlNIpbwi1Ms7PqI/edit?usp=sharing&ouid=11317... 
Thanks in Advance!
@v-zhangti 

[v-yangliu-msft · Accepted solution]

Hi  @Anonymous ,

Your sample data only has the permission to view, and you cannot copy the data. I created some data based on the sample:

Performance Table：2019 – 6 - 1 and 2019 – 7 - 17.

Transaction Table：2019 – 6 - 1 and 2019 – 6 – 30.

Here are the steps you can follow：

1. Create measure.

Flag =
var _1=MONTH(MAX('Performance Table'[Performance Date Time]))
var _2=MONTH(MAX('Transaction Table'[Transaction Date Key]))
return
if(_1 = _2&&AND(_1 <> BLANK() , _2 <>BLANK()) ,1,0)

2. Place [Flag] in Filter, set is=1, and apply filter.

3. Result:

There is no data for July in the visual object:

Best Regards,

Liu Yang

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

[Anonymous]

Hi @v-yangliu-msft,
The flag is working perfectly fine. Thanks a lot.
Can you please also help me get the CountofRows so that I can calculate the average of the difference between the two dates?

Average Days Sale to Event =

VAR TransactionDate = MONTH(MAX('Transaction'[Transaction Date Key]))

VAR EventDate = MONTH(MAX('Performance'[Performance Date Time]))

VAR FLag = if(TransactionDate = EventDate &&AND(TransactionDate <> BLANK() , EventDate <>BLANK()) ,1,0)

VAR DaysDiff = IF(Flag = 1, CALCULATE(DATEDIFF(MAX('Transaction'[Transaction Date Key]),MAX('Performance'[Performance Date Time]),DAY)))

RETURN

CALCULATE( AVERAGE(DaysDiff), FILTER ( ALLSELECTED ('Transaction Date' ), 'Transaction Date'[Month Year] = MAX ( 'Transaction Date'[Month Year] ) )

)
Here, as you know Average accepts only a column name as an argument, how can I calculate the Average of the Days difference. Final Output looks like below:
Apologies for the permission to the sample file, I have now changed it to Editor.
https://docs.google.com/spreadsheets/d/1xmg36d-68GnFWTj8cHlNIpbwi1Ms7PqI/edit?usp=sharing&ouid=11317...