Running Totals with sales table dates and date table dates

[Anonymous]

Hello,

I'm trying to write some fixed time period Running Total measures (e.g. 90-Day Revenue) and have had some issues stemming from having a date table that runs out a couple of years in front of the sales table.

I'm using this pattern to limit the dates to when I have sales:

90-Day Sales =

CALCULATE(

[Sales],

DATESINPERIOD(Date_Table[Date], MAXX(Sales_Table, Sales_Table[Date],

-90,

DAY)

When the filter context is my whole company, this measure works fine.  Here is a typical visualization of one of these measures (running 90-Day Distribution by Week Ending):

However, when the filter context includes specific items or territories, the MAXX(Sales_Table, Sales_Table[Date]) will refer to different dates for each row, throwing off totals.

I want to refer to dates no later than the last day of sales, and then I want to see the last 90 days of sales for each row from that date (not from the individual row's last sales date).

Thanks for your help,

Ben

[Anonymous]

Hi @amitchandak , isn't that the syntax I am already using?  Not sure what I've left out.

Thanks,

Ben

[amitchandak]

@Anonymous , Try a measure like

CALCULATE([Sales],DATESINPERIOD(Date_Table[Date], MAXX(Sales_Table, Sales_Table[Date]),-90,DAY))