Forum Discussion

rdini8's avatar
rdini8
Helper I
3 years ago

Making Legend Dynamic Corresponding to DAX calculation

I have four regions, East, West, South and Central. All regions have sales data from the year 2014 except Central which has from 2013.

 

I need to calculate the sales for the earliest year and one for the latest year.

 

So, I have created two measures one for earliest year and another for latest year

 

Salesearliest = CALCULATE(SUM(Orders[Sales]),(YEAR(Orders[Order Date]) = YEAR((MIN(Orders[Order Date])))))
Saleslatest = CALCULATE(SUM(Orders[Sales]),(YEAR(Orders[Order Date]) = YEAR((MAX(Orders[Order Date])))))
 
and created a parameter as
 
Saless =
{
    (YEAR(MIN(Orders[Order Date])), NAMEOF('Orders'[Salesearliest]), 0),
    (YEAR(MAX(Orders[Order Date])), NAMEOF('Orders'[Saleslatest]), 1)
}
 
After putting Saless parameter in the Y-Axis and Region in the X-axis I get this graph
 
 
So sales for Central is from 2013 and the Rest are from 2014 but in my legend is showing only 2013 and 2017.
Although the calculation is correct I want the rest of the years to be differentiated by a different colour.
Is there any possible way?
Thanks!
 

1 Reply

  • It is difficult one idea is to change the legend in "earliest year" and "latest year" via a additonal column.

    And make a dynamic textbox next to the bar chart to explain it: earlist year 2014 for B, 2013 for A