Sum sales last X days from max Fact Table Date

[jpt1228]

Hello, I have been unable to get this measure to work yet.

I have a Fact table with a field called [Sales] which also has a date colum [Date]. I have realized I don't need a date table becasue this is a very simple data model and I don't have multiple fact tables in the model. The data is updated every 4 weeks and is static (see below). By default I am filtering the report on the max date which is working. Lets call that MaxDate measure.

What I cannot figure out is how to build additional measures to take the max date and sum the sales for the last 12 weeks, 24 weeks from the max date. There is no relation to the actual current date. 

What I would be looking to do is have a measure for sales last 12 weeks from max date which would include sales for Dec 3, Nov 5, and Oct 8th. Since the data is not reported by months I think I need to sum the sales in 4  week increments

[v-caliao-msft · Accepted solution]

@jpt1228,

To achieve your requirement, you can refer to following steps:

1. Create a calculated column for the max date in your Fact table.
   
2. Create a calculated table to calculate the total sales for last 12 or 14 weeks. Expression is like this:
   Sales Calculate table = SUMMARIZE(Sales,Sales[Max Date],
   "Total Sales for last 12 weeks",CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[Date] > Sales[Max Date] - 84)),
   "Total Sales for last 24 weeks",CALCULATE(SUM(Sales[Sales]),FILTER(Sales,Sales[Date] > Sales[Max Date] - 168)))
   

Regards,

Charlie Liao

[jpt1228]

Hi @v-caliao-msft I was able to get this working however, when I tried to re-purpose it into a page filter with buttons Last 4 Weeks, Last 12 Weeks, and Last 26 weeks it doesn't work.

Basically the data is available in 4 week time periods as stated before but I want the ability to filter the visuilization on these time measures. Is there a way to create these measures to filter off of the max date in the fact table?