last 12 months data based on Period selection using prompt value

[KKR]

Hi,

I have requirement like if i select Period Name as Mar-2024 in prompt , dashboard should return data from Feb -2023 to Mar-2024.

if i select Period Name as Feb-2024 in prompt , dashboard should return data from Jan -2023 to Feb-2024,

Please let me know how to achieve this in graph?

[RADSr · Accepted solution]

You need the period name text for which you want to prompt to be associated with a date ( or something calculable ) so you can create filter logic something like

AND ( <transactiondate> <= <datefromprompt> , <transactiondate> >= add_months(<datefromprompt>, -12 ) 

***  the above is using "date" but you can use month begin date, month end date, yearmonth, etc.

If you don't yet have a calendar dimension in your model you will want one.   It makes relative time stuff approximately a million times easier than not having one  😉  

[RADSr]

Here's a quick example using Online Store which isn't universal  ( I created the date via formula ) but will give an idea of how it can be accomplished.

I'll add that if this is for a customer and not just an authoring exercise it is very important to get the requirements correct - and that starts with the business.   Imagine in the screen cap below the consumer had chosen 2024-03 for the reporting period -- the insight would then be comparing 11 *full* months, to 1 month only 7 days old.   It may be that the actual requirement is for a last 12 full month comparison.   

I'll also emphasize that if I were designing something like this for a customer I would start at the schema and import or create a date dimension.     Relative time and other date manipulations are much easier, you can incorporate fiscal calendars, company holidays, etc.    Not to mention acting as a conformed dimension for stitch queries. 

HTH,

[KKR]

@RADSr Thank you for your information.