Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Thanks
N
Solved! Go to Solution.
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
Hi amitchandak,
Thanks for this. it partially worked. I was wondering if the two field of 12 weeks and 28 days would remain static if i selected date ranges before 12 weeks. At the moment they seem to disappear if i select date ranges before begining of this year as the last date.
Thanks
N
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |