cancel
Showing results for
Did you mean:
Member

## 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
Moderator

## 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
Moderator

## 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

Member

## 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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 41 members 1,055 guests
Recent signins: