So I added this calculation to format the date to the required level
[Select Period Label - SR]
WHEN 'Year' THEN STR(YEAR([Select Period]))
WHEN 'Month' THEN LEFT(STR(DATENAME('month',[Select Period])),3) + '-' + RIGHT(STR(YEAR([Select Period])),2)
WHEN 'Quarter' THEN STR(YEAR([Select Period]))+" Q"+ DATENAME('quarter',[Select Period])
and then use this in the chart.
I also (for completeness) had to set a sort on this field (as it's no longer an actual date, but a string ...it will be sorted alphabetically, so this just ensures the dates remain in the right order)
Hope that does the trick and helps.
Dynamic_Dates_SR01.twbx 445.8 KB
Find my approach,
little modification in your parameter, values in lower case so that it can be directly use in the calculation
To link this parameter in a calculated field write this logic
DATE(DATETRUNC([Period (copy)],[Order Date]))
Now check, drag the field and make it continuous
workbook attached for your reference. Let us know if this help.
Dynamic_Date_v10.5.twbx 468.8 KB
Thanks for the help but the X-Axis does not change based on the Parameter value. If we select Quarter in the Paramter the x-axis should display Q1, Q2, Q3, Q4 instead of years or months.
Please check Simons's workbook.
The x-axis displays the desired result but when I compare the sum of Sales Target of all the Quarter in a year vs Year it does not match.
For Example: Q1 + Q2 + Q3 + Q4 for 2018 is not equal to Year 2018
I think that's because you alter the actual dates in your period formula
WHEN "Month" THEN Date(DATETRUNC('month',[Order Date]))
WHEN "Quarter" THEN Date(DATETRUNC('quarter',DATEADD('month',3,[Order Date]))-1)
WHEN "Year" THEN Date(DATETRUNC('year',DATEADD('month',6,[Order Date])))
If I select year (year of your period) 2018 and choose quarter get this
but if I select year I get
as you are adding 6 months to the actual date and then doing the date trunc on that date.
If I remove all the DATEADDs, it works consistently. Version with DATEADDS removed
Dynamic_Dates_SR01.twbx 463.8 KB
Simon, this is elegant! a very nice work around for a long standing frustration. Cheers!