Forum Discussion

jshinnenkamp's avatar
jshinnenkamp
Frequent Visitor
8 years ago
Solved

Editing contant lines based on dates

    I have this chart with a constant line at 4. The chart is using relative date filtering so 2018 months will come into the chart as they occur. I would like to be able to change the consta...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Sorry, my bad, wrap your YEAR() in a MAX function. It's a measure, so you need to do some kind of aggregation.

     

    Measure = IF(MAX(YEAR(Table[Date]))<2018,4,4.5)
Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

I would go with a combination line/column chart and use a measure for the line like:

 

Measure = IF(YEAR(Table[Date])<2018,4,4.5)
jshinnenkamp's avatar
jshinnenkamp
Frequent Visitor
8 years ago

The issue with that is my date column has multiple values so it gives me an error. I think I'd need to return a column that returns a 4.5 for the first entry in 2018 and blank in the rest of 2018 values. Similarly return a 4 for the first non 2018 date and the rest blanks. Any ideas?

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Sorry, my bad, wrap your YEAR() in a MAX function. It's a measure, so you need to do some kind of aggregation.

     

    Measure = IF(MAX(YEAR(Table[Date]))<2018,4,4.5)
  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi jshinnenkamp,

    Please follow the solution Greg_Deckler posted. Please share the sample table and list the expected result if you still have other issues.

    Best Regards,
    Angelia