Bar Chart: Last 6 months from Max([Stop Arrival Date])

[JustinDallas]

Hello Folks,

I think I've outsmarted myself and I can't think my way out of this problem.  I have delivery stops, that have a field called [Stop Arrival Date].  What I would like, is that I have a Bar Chart that looks at the Max([Stop Arrival Date]) of the possible [Stop Arrival Date] (taking into account user selections) and then it shows 3 months back worth of data.

Right now, the issue is that I have to Measure to take into account the selection of the Max([Stop Arrival Date]) but ignore the selection when bringing back data i.e when I click March it only brings back data for March, and not February and January.  Currently, I have the following.

Dimension:

=If( [Stop Arrival DateTime] >
     date(interval(Max(Total [Stop Arrival DateTime]  - 120,'D'),'YYYY/MM/DD') ),
     StopMonth )

Measure:

 SUM({1<IsEmpty={1},[Stop Arrival Date]= {'>=$(=date(Max(TOTAL [Stop Arrival Date]) - 120))'}>} stp_lgh_mileage)

The output?  This mess right here with every month under the sun. Selection={Year = 2017, StopMonth = Mar}.

Any help is greatly appreciated.

[sunny_talwar · Accepted solution]

May be this

=If([Stop Arrival DateTime] > (Max(Total [Stop Arrival DateTime])  - 120), StopMonth)

[sunny_talwar]

And this measure

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

[sunny_talwar]

And this measure

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

[JustinDallas]

I'm a blistering idiot.  The [Stop Arrival DateTime] and the [Stop Month] field reside on different tables.  This leads to their connecting key being very important, but also very hidden since I thought they were on the same table.  This was my final solution.

Dimension:

=If([StopCalendarDate] > Date(Max(Total [StopCalendarDate])  - 120)

AND [StopCalendarDate] <= Date(Max(Total [StopCalendarDate])), StopMonth)

Measure:

SUM({1<IsEmpty={1}>} stp_lgh_mileage)

[sunny_talwar]

You don't really need formatting function such as Date() in your if condition. If this makes viewing easy, then you can continue using it... but this should do also

=If([StopCalendarDate] > (Max(Total [StopCalendarDate])  - 120)

AND [StopCalendarDate] <= (Max(Total [StopCalendarDate])), StopMonth)