Display a dynamic last 12 months in a line chart?

[rduregger]

Hi there, 

I am developing a line chart where I will need to display the last 12 months on the X axis. The field I need to use for this,  DateField,  is a timestamp ('M/D/YYYY h:mm:ss TT',  e.g. 1/1/2018 11:04:35 AM) in the data model.

Ideally, the chart will default to the last 12 months relative to the present day's date (e.g., if today is 12 October 2022, it will show data for 12 October 2022 thru 12 October 2021). Additionally, it should respond to a user selection on the date field (e.g., user selects 16 July 2022 and the chart updates to show 16 July 2022 thru 16 July 2021). 

While I've read similar posts to this forum, I haven't come across a post yet where I can tailor the solution to my needs. Would anyone be able to point me in the right direction in terms of scripting required for this? Happy to share additional explanation to the requirements and also include sample data, if helpful. 

Thanks so much! 

[MayilVahanan · Accepted solution]

Hi

We can bring datefield without timestamp from data model and then try like below

Sum({<Date={">=$(=AddMonths(Max(Date),-12))<=$(=Date(Max(Date)))"},MonthYear=, Year=, Month=, Quarter=>}urmeasure)

[rduregger]

Thank you Mayil! I was able to achieve my goal by taking an approach similar to what you outlined above, just with the use of variables. See below for what I did: 

1. Create variable vMaxDate, definition: =Date(Max(YourDateField))
2. Create variable vR12_Start, definition: Date(Floor(MonthStart(AddMonths(vMaxDate,-12))))
3. Create variable vMonthEnd, definition: Date(Floor(MonthEnd(vMaxDate)))
4. Create variable vIgnoreTimeLine, definition: ='YourDateField1=, YourDatefield2=, YourDateField3='
5. Create variable vR12_Set, definition: 'YourDateField= {">='&$(vR12_Start)&'"}*{"<='&$(vMonthEnd)&'"},'&vIgnoreTimeLine

Then, for whatever measure you are constraining by the rolling 12 months can be written like: 

sum({$<$(=$(vR12_Set))>}

    YourMeasure

    )