cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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

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

1 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

View solution in original post

3 REPLIES 3
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

View solution in original post

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.