cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adnanzakir
Helper III
Helper III

FILTER ALLSELECTED HELP

Hi Guys. 

I am working with a table which has placements. The data is from 2015, In order to calculate the conversion rate, I need to select records from 2015 uptto 60 days prior from today (which is going to rolling on daily basis) .

 

I had been using the following measure which was working fine: 

Measure= CALCULATE(<claim ID / Placements measure or coulmn>,FILTER(ALLSELECTED(Sales),Sales[Date]<=DATE(YEAR(TODAY()), MONTH(TODAY())-2, DAY(TODAY()))))

 

What I would like to do now is calculate the same conversion, however, instead of using date from 2015, use in the last 12 months. So basically, calculate conversion last 12 months from today up to 60 days prior from today.  

 

DATEADD Function @v-xicai (Thanks for your help previously Amy, any help on the new problem would be appreciated)

1 ACCEPTED SOLUTION

Hi @adnanzakir ,

 

Is this what you want?

v-lionel-msft_0-1606804281463.pngv-lionel-msft_1-1606804311586.png

v-lionel-msft_2-1606804326203.png

 

Best regards,
Lionel Chen

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

View solution in original post

7 REPLIES 7
v-lionel-msft
Community Support
Community Support

Hi @adnanzakir ,

 

Try this.

Measure = 
VAR __last_12_months = 
CALCULATE(
    SUM('Table'[Value]),
    DATESINPERIOD(
        'Table'[Date],
        TODAY(),
        -12,
        MONTH
    )
)
VAR __todayUpTo60days = 
CALCULATE(
    SUM('Table'[Value]),
    DATESINPERIOD(
        'Table'[Date],
        TODAY(),
        -60,
        DAY
    )
)
RETURN
__last_12_months - __todayUpTo60days

v-lionel-msft_0-1605508081407.png

Or this.

Measure 2 = 
CALCULATE(
    SUM('Table'[Value]),
    DATESINPERIOD(
        'Table'[Date],
        TODAY()-60,
        -12, 
        MONTH
    )
)

v-lionel-msft_1-1605508169329.png

 

Best regards,
Lionel Chen

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

 

 

Hi @v-lionel-msft . Thanks for your reply. I tried the second formula, it works fine limiting the data to last 12 months, but does not stop at 60 days from today. Instead it shows the informating all the way to today. 

Hi @adnanzakir ,

 

Is this what you want?

v-lionel-msft_0-1606804281463.pngv-lionel-msft_1-1606804311586.png

v-lionel-msft_2-1606804326203.png

 

Best regards,
Lionel Chen

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

View solution in original post

Hi @v-lionel-msft . Exactly what I needed. Thank you for your help. 

adnanzakir
Helper III
Helper III

@v-xicai , you helped out with this formula previously, any idea if we can alter it in a way that it only starts from past 12 months from today:

amitchandak
Super User IV
Super User IV

@adnanzakir , Hope you have date column and date table. This means 12 months till 60 datys before

Rolling 12 till last 60 day= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-60,Day)),-12,MONTH))

 

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 @amitchandak thanks for your reply. I am not really using a date table, it's just one of the columns in my report that has dates in it. I used your formula and it came up with the following error:

"The MAX function only accepts a column reference as an argument."

On that note, previously I had been using the following formula which had been working fine, but as this formula uses dates from 2015, I was looking to alter it in a way that it only starts from past 12 months from today:

Measure: CALCULATE (<claim ID / Placements measure or coulmn>, FILTER (ALLSELECTED (Sales), Sales [Date] <- DATE (YEAR (TODAY ()), MONTH (TODAY ()) - 2, DAY (TODAY () ))))

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors