Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Making DATESBETWEEN dynamic

I am trying to get a table based on DATESBETWEEN, when I am using TODAY() I get the desired output, but when I try to use the values captured from a slicer as parameters in DATESBETWEEN I am not getting any output. The "datetime" mentioned below is table with months and years like "March 2022" as a column, this is also used on the slicer I want to capture the values from. If I used VAR d = DATE(2022, 06, 30) the code works.

This is my code:

Period =
 
VAR y = YEAR(SELECTEDVALUE(datetime[Value]))
VAR m = MONTH(SELECTEDVALUE(datetime[Value]))
VAR x = 30
VAR d = DATE(y, m, x)
 
VAR lastonemonth = ADDCOLUMNS(
CALCULATETABLE( 'Calendar'
,DATESBETWEEN('Calendar'[Date], d - 30, d )
)
,"Timeframe","R1"
)
VAR lastthreemonths = ADDCOLUMNS(
CALCULATETABLE( 'Calendar'
,DATESBETWEEN('Calendar'[Date], d - 90, d)
)
,"Timeframe","R3"
)
VAR lastsixmonths = ADDCOLUMNS(
CALCULATETABLE( 'Calendar'
,DATESBETWEEN('Calendar'[Date], d -180 , d )
)
,"Timeframe","R6"
)
VAR lasttwelvemonths = ADDCOLUMNS(
CALCULATETABLE( 'Calendar'
,DATESBETWEEN('Calendar'[Date], d - 365 , d )
)
,"Timeframe","R12"
)

RETURN
UNION(lastonemonth,lastthreemonths, lastsixmonths, lasttwelvemonths)
 
Thanks.
Need Help
  • AntonioM's avatar
    AntonioM
    4 years ago

    Are you creating a calculated data table here? These are only changed when you refresh, so won't update dynamically as you interact with the report. This means where you've used SELECTEDVALUE there is no selectedvalue because the table doesn't 'see' any slicers.

     

    Could you use time intelligence in your measures instead? Something like

    'measure' last month =
VAR d = EOMONTH (SELECTEDVALUE(datetime[Value]), 0)
CALCULATE ( [Measure], DATESBETWEEN ( 'Calendar'[Date], d - 30, d )

     

    You could maybe try filtering the date tabe using a measure, something that gets the selected timeframe and flags the dates which are within that timeframe from the slicer date.

1 Reply

  • AntonioM's avatar
    AntonioM
    Solution Sage
    4 years ago

    Are you creating a calculated data table here? These are only changed when you refresh, so won't update dynamically as you interact with the report. This means where you've used SELECTEDVALUE there is no selectedvalue because the table doesn't 'see' any slicers.

     

    Could you use time intelligence in your measures instead? Something like

    'measure' last month =
VAR d = EOMONTH (SELECTEDVALUE(datetime[Value]), 0)
CALCULATE ( [Measure], DATESBETWEEN ( 'Calendar'[Date], d - 30, d )

     

    You could maybe try filtering the date tabe using a measure, something that gets the selected timeframe and flags the dates which are within that timeframe from the slicer date.