cancel
Showing results for
Did you mean:
Highlighted
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
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
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!

#### Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors