Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ngiom
Frequent Visitor

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.

Thanks

N

Capture.PNG

1 ACCEPTED 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

View solution in original post

3 REPLIES 3
ngiom
Frequent Visitor

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

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.