Add a field to your calendar table using this expression
Date fields are special and contain a numeric value and the numeric equivalent so if you advance your detail date to the Month End Date then you will only get one value per month but since you will be using the dual properties of the date data type you will have the numbers 1-12 displayed but the actual values will be equivalent to the appropriate month ending dates.
Oh, so you want to use the formula as a chart dimension.
And I did not pay attention to your sample data, sorry about that.
The easiest thing to do would be to add a date field to your spreadsheet, let's call it NSV_Date (my best guess based on your columns)
then you would have in your load script
Date(MonthEnd(Date(DateField)),'M') as [Distinct Month]
Or you should be able to do this with the data you have, however this is more tricky and requires a calcualted dimension which I would not reccommend.
Date(MonthEnd(Date(Num([Month] & '/1/' & [Year]))),'M')
Hope this helps
As per my understanding of your query you just need to set your charts multi dimensions in proper order. PFA.
The charts use Year & Month as dimensions, and the CM field as expression. Probably the second chart would be better, as it uses Month as primary dimension and year as secondary. This would also be better in UI perspective as the bars wont be crowded because the months will remain fixed i.e 12 months Jan-Dec, while the number of years would depend on your data. Incase, using Year as primary dimension, do set your Dimension limitations under Presentation tab of chart properties. Yeah, but do setup your calendar stuff first, incase you creating master calendar.
Is this what you were looking for? Or have I completely misunderstood, do let me know.
Test.qvw 151.0 K
Probably, you are doing a Sum(), so its summing up data for all months in the year and then displaying values per year only. May be you can try drilling down further by creating a Weekday dimension or so, you may try that out.
Secondly, incase you are showcasing data varying over time, its better to use line charts. Play around with your charts dimensions and you will get to the answer.