Forum Discussion

Jsevilleja's avatar
Jsevilleja
Frequent Visitor
2 years ago

Variable End dates for DATESBETWEEN function

Hi Everyone, this is the first question will throw in here. Been searching through various forums of similar issue but could not found anything online so here it goes. 

 

I am trying to have Calendar Range to be variable.

 

 = DATESBETWEEN('Calendar'[Date],TODAY(),'Calendar'[1 Week])
 
Yes, the End date is a measure driven by a switch function (Whole number). Unfortunately it does not work which I am curious why.
 
That 'Calendar'[1 Week] measure is just 
= TODAY()+'Calendar Week Look Ahead'[Week Variable]
'Calendar Week Look Ahead'[Week Variable]   ===> Switch function drives to give (7,14,21,28 depending on what is selected in the slicer) 
 
however if I replace this with 
= TODAY()+7  it works. 
 
And when I put this 'Calendar'[1 Week] on any visuals, the dates are working. Only time it does not work is when I am trying to use them in DATESBETWEEN. 
 
I hope I made it clear. I hgihly appreciate any comments. I starting to believe Time Intelligence function does not accept measures such as this but why does TODAY() + Measure function works well?
 
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    Jsevilleja , Hope that is selected value

     

    example

    = TODAY()+ SelectedValue('Calendar Week Look Ahead'[Week Variable])
     
    and this used in a measure, if you use that in column or table , it will not take slicer value
     
    DATESBETWEEN('Calendar'[Date],TODAY(),'Calendar'[1 Week])
     
    example
    CALCULATE([ViewsCount], DATESBETWEEN(Dates[Date],StartDate,MidDate))

  • Jsevilleja's avatar
    Jsevilleja
    Frequent Visitor
    2 years ago

    Hi amitchandak 

     

    Thanks for your quick replies! 

     

    Yes Today() function is together with a measure that uses SELECTEDVALUE

     

    to further break it down: That Selected Value is below:

     

    Week Variable =
    var _select = SELECTEDVALUE('Calendar Week Look Ahead'[Weeks])
    return
    SWITCH(
    TRUE(),
    _select="1 Week",7,
    _select="2 Weeks",14,
    _select="3 Weeks",21,
    _select="4 Weeks",28,
    _select="5 Weeks",35

    )

     

    As you can see my intention here is the Have a slicer that controls the Dates Range. TODAY() function works well as a measure together with SELECTED VALUE measure. It becomes a different story when it is inside the DATESBETWEEN. 

     

    I recreated a similar powerbi with same issue. 

    https://drive.google.com/file/d/1yYBADrkbG--5cl-dix3gLaFfY6A-_bPm/view?usp=drive_link

     

    Here are the measure that works (Table or Card Visual:

    • Selval = SELECTEDVALUE('Select'[Selection],0)
    • Variable Day = TODAY()+'Select'[Selval]
    • Variable Dates Manual = DATESBETWEEN('Calendar'[Date],TODAY(),TODAY()+15)
     
    Here is the measure that does not work
    • Variable Dates = DATESBETWEEN('Calendar'[Date],TODAY(),TODAY()+'Select'[Selval])

     

    Does this mean SELECTEDVALUE cannot be used for DATESBETWEEN if SELECTEDVALUE is picking up from a column or table?

     

    Thanks again