cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jpt1228 Member
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

 

 

image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Moderator v-caliao-msft
Moderator

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

@jpt1228,

 

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

  1. Create a calculated column for the max date in your Fact table.
    Capture1.PNG
  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)))
    Capture2.PNG

Regards,

Charlie Liao

View solution in original post

2 REPLIES 2
Highlighted
Moderator v-caliao-msft
Moderator

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

@jpt1228,

 

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

  1. Create a calculated column for the max date in your Fact table.
    Capture1.PNG
  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)))
    Capture2.PNG

Regards,

Charlie Liao

View solution in original post

jpt1228 Member
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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

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

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

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.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

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

Users Online
Currently online: 41 members 1,055 guests
Please welcome our newest community members: