FILTER ALLSELECTED HELP

[Anonymous]

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)

[v-lionel-msft]

Hi @Anonymous ,

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

Or this.

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

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.

[Anonymous]

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. 

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

Is this what you want?

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.

[Anonymous]

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

[Anonymous]

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

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

[Anonymous]

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 () ))))