Forum Discussion
Help with Clustered Column Chart
So the issue is your chart is relying on the column series to provide the yearly filter. But there is no such filtering on your line measure. If you specifically want (ie always want) 2015, just write a measure like this and replace it for your line
=calculate([current line measure],YourCalendarTable[Year] = 2015)
If you somehow want the line to be the "previous year", then you would need something like this.
=calculate([current line measure],YourCalendarTable[Year] = max(YourCalendarTable[Year])-1)
Thanks MattAllington for your help on this. I really appreciate it.
I have created a date dimension table to use in the measure. I however run into another error message: "A single value for column 'Set Date' in table 'Meters Sold vs Set' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
I am counting a number of occurance in the Set Date column as a measure here. This field is a date field (mm/dd/yyyy). I am wondering if we need a countable field to be referenced here?
Set Date 2015 = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), 'Date Dimension'[Year] = 2015)
or try should give you same result
Set Date 2015 = CALCULATE(COUNTROWS('Meters Sold vs Set'), 'Date Dimension'[Year] = 2015)
Thanks Sean for your help on this.
I had changed the formular. However, I seam to have two identical lines. In other words, the fomular does not seem to alter my result. Would you please take anther look at the screen below.
here is my fomular: Set Date 2015 = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), 'DateKey'[Year] = 2015)
Thanks Sean
AndrewDang You have 2 options (each has its advantages)
OPTION 1
Create another Measure using the same formula just change 2015 to 2016
Set Date 2016 = ...... just change 2016 at the end
OPTON 2
You can also create (CY- Current Year and PY - Previous Year) - WATCH the results if you have Year Slicer though !!!
Set Date PY = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), FILTER('DateKey', 'DateKey'[Year] = MAX('DateKey'[Year])-1))
Set Date CY = CALCULATE(COUNTA('Meters Sold vs Set'[Set Date]), FILTER('DateKey', 'DateKey'[Year] = MAX('DateKey'[Year])))
Let me know if this works!
AndrewDang wrote:I have created a date dimension table to use in the measure. I however run into another error message: "A single value for column 'Set Date' in table 'Meters Sold vs Set' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
sorry, my bad mistake. The second formula should be
=calculate([current line measure],filter(YourCalendarTable,YourCalendarTable[Year] = max(YourCalendarTable[Year])-1))
Replace [current line measure] with what ever you are trying to do (counting something in this case).
update the table and column names too of course
