Showing Only Max Avg Value on TTM Graph

[atowriss99]

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!

[amitchandak · Accepted solution]

@atowriss99 , 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

[amitchandak · Accepted solution]

@atowriss99 , 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

[atowriss99 · Accepted solution]

@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())

[atowriss99]

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

[atowriss99]

Unfortunately, this still gives me a constant line and makes my graph go beyond my Trailing 12 months

[atowriss99]

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!