Querying current quarter / month / year data MINUS the last x days

[janinw]

Hi all,

I would like to get the result of a certain expression/calculation for the current quarter, not considering the last 30 days of the quarter. 

I have tried the following:

cr_rate = calculate(
totalqtd(divide(sum(report[num_appm]), sum(report[num_leads])),report[date]),
report[date] < today()-30)

cr_rate = calculate(
divide(sum(report[num_appm]), sum(report[num_leads])), 
datesqtd(report[date]), report[date] < today()-30)

(and a few other creative combinations I cannot currently remember)

But it I am not getting the result as the one when I do it manually with a date filter.

Many thanks for your help,

Janine

[amitchandak]

@janinw , Kudos to you. Thanks for let us know.

[amitchandak]

@janinw , Prefer a date table and try like given example

QTD QTY forced=
var _max = today()-30
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESQTD('Date'[Date])), blank())

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[janinw · Accepted solution]

Hi,

I figured it out eventually - your solution @amitchandak did not seem to work eventually, but you give me some new inspiration (e.g. adding date table) and with:

calculate(totalqtd(divide(sum(report[num_appm]), sum(report[num_leads])),'date'[Date]), 'date'[Date] <= today()-30) 

it seemed to work eventually.

Best,

Janine