Dynamic Text Box with Date Range

[GJ217]

Hi,

I would like to add a text box that  dynamically shows the rolling 12 month date range in my Month & Year page filter which is set to Relative Date, is in the last 12 calendar months.

For example I would like the Text box to read as below:

Starters Diversity - Rolling 12 Months  01/12/2021 - 30/11/2022

Would be very grateful for any help with this.

[amitchandak · Accepted solution]

@GJ217 , You can create a measure and use that in text box, make sure Q&A feature is enabled

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = eomonth(_max, -12) +1

return

"Rolling 12 Month " & _mn & " to " _max

[GJ217]

@amitchandak  this is perfect thank you!

[amitchandak · Accepted solution]

@GJ217 , You can create a measure and use that in text box, make sure Q&A feature is enabled

new measure =
var _max = maxx(allselected(Date),Date[Date])
var _min = eomonth(_max, -12) +1

return

"Rolling 12 Month " & _mn & " to " _max

[GJ217]

Just a quick question how can I get the dates to show DD/MM/YYYY format?

[GJ217 · Accepted solution]

Managed to get this to work with date format in DD/MM/YYYY for UK format.

Rolling 12Mth Title =

VAR _Max = FORMAT(MAXX(ALLSELECTED(Dates),Dates[Date]),"DD/MM/YYYY")

VAR _Min = EOMONTH(_MAX,-12)+1

RETURN

FORMAT(_Min,"DD/MM/YYYY")&" to "&_Max