Calculate a ratio for previous month

[janinw]

Hi all, 

I have read several related topics, but have not found the answer (apparently).

I want to calculate a certain ratio between two metrics for the past month. I created the calculated measure below in DAX:

rate_leads_to_sale_prev_month = CALCULATE(DIVIDE([num_sales], [num_leads], 0), PREVIOUSMONTH('date'[Date]))

 
However, it does not give me the correct value.

The active relationship is between the [Date] column from the 'date' table and a date column from the main table (1:*) containing the metrics.

Best,
Janine

[amitchandak · Accepted solution]

@janinw, they seem fine, the only other version can be

last MTD Sales ? CALCULATE(DIVIDE([num_sales], [num_leads], 0),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Can you share sample data and sample output in table format? Or a sample of pbix after deleting sensitive data.

[janinw]

Hi Amit,

this seems to do the trick, thanks much! This also seems to work with PREVIOUSMONTH(DATESMTD('Date'[Date])).

Since I would really like to understand why my intial DAX code did not work - could you explain to me what the issue might be with this?

Upon some more reading after receiving your solution I now seem to understand what DATESMTD does, however I still do not see why it does not work without.

Thanks!

Janine