Show 12 months when One month is selected

[natisham]

I have a chart trying to show the last 12 months beginning with the month I select. My current dimension is MonthYear.

My expression is sum({<MonthYear=>}[MACHINE HOURS])* avg({<MonthYear=>}[WORK PCT]/100)

I have it down to ignore the month year I select to show past months. But my chart keeps showing from Jun 2017 back to Feb 2016. How can I have the chart show all 12 months starting with my selected monthyear.

[sunny_talwar]

How do you create MonthYear field in the script? Assuming it is created like this

Date(MonthStart(DateField), 'MMM YYYY') as MonthYear

Try this

Sum({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -12), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Year>} [MACHINE HOURS])* Avg({<MonthYear = {"$(='>=' & Date(AddMonths(Max(MonthYear), -12), 'MMM YYYY') & '<=' & Date(Max(MonthYear), 'MMM YYYY'))"}, Year>} [WORK PCT]/100)

[natisham]

I get an error in expression.

[sunny_talwar]

And what is the error say

[effinty2112]

Hi Natasha,

This is easy if you have a master calendar with a sequential numerical field for your months, not resetting at a new year. So if Dec 2016 had MonthID 67 say Jan 2017 would be 68.

If you have a MonthID like that then something like

sum({$<MonthID = {">='$(=-12+max(MonthID))'"}, MonthYear=>}[MACHINE HOURS]) might work for you.

There's bound to be a syntax error in there but I hope you get the gist.

cheers

Andrew

[natisham]

My monthyear in the calendar is date(monthstart(TempDate), 'MMM-YYYY') as MonthYear.

I updated expression to : Sum({<MonthYear = {"$(='>=' & date(AddMonths(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & date(Max(MonthYear), 'MMM-YYYY'))"}, Year>} [MACHINE HOURS])* Avg({<MonthYear = {"$(='>=' & date(AddMonths(Max(MonthYear), -12), 'MMM-YYYY') & '<=' & date(Max(MonthYear), 'MMM-YYYY'))"}, Year>} [WORK PCT]/100)

when i change the MonthYear on my list box the data is changing for each month and getting incorrect data.

[sunny_talwar]

So based on your selection in MonthYear, wouldn't the number change because your are going to see 12 months from the selected date?? Is that not what you want? What are you hoping to see if Jan2017 is selected?

[natisham]

If Jan 2017 is selected then I would like to see Jan 2017 - Jan 2016. It is a bar graph, so each bar would represent a different month. So when i select Feb 2017. The bars represent feb 2017-feb 2016.

[sunny_talwar]

Okay so now we know what you want, can you tell me what the above expression is doing when you select Jan 2017? It is not showing Jan 2017 - Jan 2016? Showing another range of period?

[natisham]

When I click on Jan 2017 I see Feb 2016 to June 2017. If I click on Feb 2017, the same months Feb - June stay but the data changes. When i used expression :

sum({<MonthYear=>}[MACHINE HOURS])* avg({<MonthYear=>}[WORK PCT]/100)

I got the right data but still showed feb 2016 to June 2017 no matter what month i clicked on.