cancel
Showing results for
Did you mean:
Post Patron

## Sum sales last X days from max Fact Table Date

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Sum sales last X days from max Fact Table Date

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

2 REPLIES 2
Highlighted
Microsoft

## Re: Sum sales last X days from max Fact Table Date

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

Highlighted
Post Patron

## Re: Sum sales last X days from max Fact Table Date

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?

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors