cancel
Showing results for 
Search instead for 
Did you mean: 
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!

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
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!