Showing Only Max Avg Value on TTM Graph

Hi,

I have a TTM graph that has 4 lines based on measures. I was able to create measures to show the last month's values as data labels on my graph, but also want to show Max and Min values. My last sales month value shows up as a point, which I thrn turn on the data labels for.

However, when I use the below DAX to get the Max, it not only creates a constant line (at the correct value), as opposed to a point, but causes my graph to no longer only show the Trailing Month, but instead goes back all the way to the beginning date of my data. I've tried multiple suggestions found on here and Google, and nothing has worked.

Is what I'm looking to do impossible? Below is my measure. I have both a date and disconnected date table and have created a summary table for my averages

Cred Max CPF =

VAR _SelDate =

Max('Date'[Date])

VAR _PV13=DATESINPERIOD('Disconnect Date'[Date],_SelDate,-13,Month)

VAR Result =

Calculate(MAX('CPF Average'[Avg]),Map_Product[Reporting Product]="Cred",'Cost Per File'[EOM]>=_PV13&&'Cost Per File'[EOM]<=_PV13,

REMOVEFILTERS('Date'),

KEEPFILTERS(_PV13),

USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date]))

Return

Result

TIA!

Need Help

amitchandak
3 years ago
Anonymous , max date would be like

Cred Max CPF =
VAR _SelDate =
MaxX(allselected('Date'), 'Date'[Date])
VAR Result =
Calculate(Calculate(MAX('CPF Average'[Avg]),Map_Product[Reporting Product]="Cred",'Cost Per File'[EOM]>=_PV13&&'Cost Per File'[EOM]<=_PV13,
USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date])), filter( 'Date', 'Date'[Date] =_SelDate))
Return
Result

and min will be

Min Date = eomonth([Cred Max CPF],13)+1 //in case you need date before 13 months
Anonymous
3 years ago
amitchandak My actual solution was this:
Var _SelDate=SELECTEDVALUE('Date'[Date])
Var _enddate=EOMONTH(_SelDate,-13)
VAR _maxcpf=calculate(max('CPF Average'[Avg]),All('CPF Average'[EOM]),DATESBETWEEN('Date'[Date],_enddate,_SelDate),'CPF Average'[Reporting Product]="Cred")
Return
If([Cred TTM Avg CPF]=_maxcpf,_maxcpf,Blank())

5 Replies

amitchandak
Super User
3 years ago
Anonymous , max date would be like

Cred Max CPF =
VAR _SelDate =
MaxX(allselected('Date'), 'Date'[Date])
VAR Result =
Calculate(Calculate(MAX('CPF Average'[Avg]),Map_Product[Reporting Product]="Cred",'Cost Per File'[EOM]>=_PV13&&'Cost Per File'[EOM]<=_PV13,
USERELATIONSHIP('Date'[Date],'Disconnect Date'[Date])), filter( 'Date', 'Date'[Date] =_SelDate))
Return
Result

and min will be

Min Date = eomonth([Cred Max CPF],13)+1 //in case you need date before 13 months
- Anonymous
  Not applicable
  3 years ago
  Unfortunately, this still gives me a constant line and makes my graph go beyond my Trailing 12 months
  - Anonymous
    Not applicable
    3 years ago
    UGH...I figured out why it was doing that...I failed to make a connecion to the date columsn in my model when I created the new table!
- Anonymous
  Not applicable
  3 years ago
  I fixed my date connection, but the measure you gave me produces the value of the last month (Max Date) on the graph, which I already have. I want a point to show the MaximumValue, which occurs in April 2022
- Anonymous
  Not applicable
  3 years ago
  amitchandak My actual solution was this:
  Var _SelDate=SELECTEDVALUE('Date'[Date])
  Var _enddate=EOMONTH(_SelDate,-13)
  VAR _maxcpf=calculate(max('CPF Average'[Avg]),All('CPF Average'[EOM]),DATESBETWEEN('Date'[Date],_enddate,_SelDate),'CPF Average'[Reporting Product]="Cred")
  Return
  If([Cred TTM Avg CPF]=_maxcpf,_maxcpf,Blank())

Forum Discussion

Showing Only Max Avg Value on TTM Graph