Forum Discussion

ngiom's avatar
ngiom
Frequent Visitor
5 years ago
Solved

Static values in a graph

Hi there, I am trying to show a graph with static values and dynamic values adjusted by a slider int he same graph. The image below has sample data showing the static 28 days and dynamic episodes. ...
Need Help
Show and Tell
Tips and Tricks
  • ngiom's avatar
    ngiom
    5 years ago

    Hi amitchandak,

    I figured it out. I created a second date table using the calendar(min(date[date]), max(date[date])) of the first table. Then created a last date field in the new date table using the TODAY() function. I then used the last date on the two measures above to make them static. Finally i used the second date table as the slide filter, see adjusted caculated fields.

     

    Static fields:

    12 weeks =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[LastDate])
    var _min = maxx(ALLSELECTED('Date2'),'Date2'[LastDate]) - 12*7
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))


    28 Days =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[LastDate])
    var _min = MAxx(ALLSELECTED('Date2'),'Date2'[LastDate]) - 28
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))

     

    Dynamic field:

    Episodes =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[Date])
    var _min = Minx(ALLSELECTED('Date2'),'Date2'[Date]) 
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))

     

    Thanks for the initial idea of the two measures.

    N

amitchandak's avatar
amitchandak
Super User
5 years ago

ngiom , Try two new measures , Assuming you are using Episodes as measure and Date from Date table on Rows of matrix

 

12 weeks =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = maxx(ALLSELECTED('Date'),'Date'[Date]) - 12*7
return
calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))


28 Days =
var _max = maxx(ALLSELECTED('Date'),'Date'[Date])
var _min = MAxx(ALLSELECTED('Date'),'Date'[Date]) - 28
return
calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))

 

 

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.

  • ngiom's avatar
    ngiom
    Frequent Visitor
    5 years ago

    Hi amitchandak,

    I figured it out. I created a second date table using the calendar(min(date[date]), max(date[date])) of the first table. Then created a last date field in the new date table using the TODAY() function. I then used the last date on the two measures above to make them static. Finally i used the second date table as the slide filter, see adjusted caculated fields.

     

    Static fields:

    12 weeks =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[LastDate])
    var _min = maxx(ALLSELECTED('Date2'),'Date2'[LastDate]) - 12*7
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))


    28 Days =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[LastDate])
    var _min = MAxx(ALLSELECTED('Date2'),'Date2'[LastDate]) - 28
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))

     

    Dynamic field:

    Episodes =
    var _max = maxx(ALLSELECTED('Date2'),'Date2'[Date])
    var _min = Minx(ALLSELECTED('Date2'),'Date2'[Date]) 
    return
    calculate([Episodes], filter(Date, Date[Date] >=Min && 'Date'[Date] <=Max ))

     

    Thanks for the initial idea of the two measures.

    N