Want to get Selected Date range (Start and End) from Date Slider Slicer in Direct Query Mode

[Anonymous]

Hi All,

I want to calculate 12 months rolling members retained. I have Date Slider Slicer in Power BI Report. 

Retention_In_12Months =

VAR CurrentDate = SELECTEDVALUE(RETENTION[fulldate])

VAR PreviousDate = CurrentDate - 365

RETURN

CALCULATE (

SUM ( RETENTION[Member_Retention] ),

FILTER (

RETENTION,

RETENTION[fulldate] >= PreviousDate

&& RETENTION[fulldate] <= CurrentDate

)

)

But It is not working.

How can I get Start and End date from Date Slider Slicer and use it in above calculation?

[Anonymous · Accepted solution]

This is how I achieved this:

SelectedDateStart = CALCULATE (

MIN (RETENTION[fulldate]),

FILTER (

ALLSELECTED (RETENTION[fulldate]),

RETENTION[fulldate] >= MIN (RETENTION[fulldate])

))

SelectedDateEnd = CALCULATE (

MAX (RETENTION[fulldate]),

FILTER (

ALLSELECTED (RETENTION[fulldate]),

RETENTION[fulldate] <= MAX (RETENTION[fulldate])

) )

Retention_In_12Months =

VAR CurrentDate = CALCULATE (

MAX (RETENTION[fulldate]),

FILTER (

ALLSELECTED (RETENTION[fulldate]),

RETENTION[fulldate] <= MAX (RETENTION[fulldate])

) )

VAR PreviousDate = CurrentDate - 365

RETURN

CALCULATE (

SUM ( RETENTION[LLC_Retention] ),

DATESBETWEEN (

RETENTION[fulldate], PreviousDate, CurrentDate

))

[amitchandak]

@Anonymous , Create a date table ( you can have that in import mode if that not in DB)

Date as import mode -https://www.youtube.com/watch?v=cQfJ0GmQ5os&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA&index=7

Date in Database - https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intelligence-in/ba-p/922885

Rolling 12 months measure

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

or

Rolling 365 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-365,Day))