@hnguyen76 - To clarify, is this correct?: Consistency in this case means that there will always be 3 columns: first column will be dark blue, second medium, and third light.
The problem with this is that you want the color for a particular quarter to change depending on its position in the chart.
One way to accomplish this is by using static categories in the Legend.
1. Create a Date Table that contains relative quarters (like the Date table in the attached pbix).
2. Create a Parameter table with the list of quarters and their relative quarters from above date table.
Parameters =
var a = SELECTCOLUMNS('Date',"Quarter Year", 'Date'[Quarter Year], "Relative Quarter",'Date'[Relative Quarter])
var b = DATATABLE(
"Selected Quarter Description", STRING,
"Selected Quarter Order", INTEGER,
{{"Previous Year", 1},{"Previous Quarter", 2},{"Selected Quarter", 3}}
)
return CROSSJOIN(a,b)
3. On the Model Pane, set the Sort By value for the various Quarter values.
4. Create a relationship between your fact table and the new Date table.
5. Create a Measure which gets the value for the relevant quarters.
Value By Relevant Quarters =
var _ThisQuarter = SELECTEDVALUE(Parameters[Relative Quarter])
var _LastQuarter = _ThisQuarter - 1
var _LastYearQuarter = _ThisQuarter - 4
return
SWITCH(
MAX('Parameters'[Selected Quarter Order]),
1, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastYearQuarter),
2, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _LastQuarter),
3, CALCULATE(SUM(DummyData[Value]), 'Date'[Relative Quarter] = _ThisQuarter)
)
6. Create a slicer from the Parameters[Quarter Year].
7. Add a Column Chart, with Parameters[Select Quarter Description] in the Legend, and [Value by Relative Quarters] for the values.
Please see attached PBIX which demonstrates the logic.
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
