Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Sales by Last 28 Days does not work by Store or Product Level

Hello, 

 

I was able to create get last sales by last 28 days, but it will not work by store level or SKU level when I drag it to the table.  Can anybody please tell me what did I do wrong over here? 

 

TotalSales = SUMX(SalesDetails,SalesDetails[Sales])

1st Method 

LAST28DAYSSALES = CALCULATE([TotalSales],
    FILTER(ALL(Calender[Date]),
        Calender[Date] > MAX(Calender[Date]) - 28 && 
        Calender[Date] <= MAX(Calender[Date])))

2nd Method 

Last28DaysSales 2 = CALCULATE([TotalSales],DATEADD(SalesDetails[Date],-28,DAY))

 

Click here for the sample file. 

 

Thank you so much

1 ACCEPTED SOLUTION

Create new date table using for date.

Values=SalesDeails[date] and get rid of calendar date table and link the relationship

This will give you sales for last 28 days but excluding any dates that has no salesCapture.PNG

View solution in original post

7 REPLIES 7
ChandeepChhabra
Impactful Individual
Impactful Individual

@Anonymous Ok so 2 things

 

  1. I am not sure why have you chosen =SUMX(SalesDetails,SalesDetails[Sales]) you can also write a simple =SUM(SalesDetails[Sales])
  2. Slightly change your Measure
    • Last28DaysSales 2 = CALCULATE([TotalSales],DATEADD(Calender[Date],-28,DAY))

Since Dateadd is a time intelligent function and it only accepts continuous dates so link it to Calendar[Date] column instead of SalesDetails[Date] Column.

 

It should work now. Hope it helps

Anonymous
Not applicable

I tried this too, but it does not work. It will give me the taotal sales amount not last 28 days. Capture.JPG

Thank you  

@Anonymous 

 

  1. I created a new Cal table related to SalesDetail table
  2. And modified the measure LAST28DAYSSALES = CALCULATE([TotalSales], DATESBETWEEN(Calender[Date],MAX(Cal[Date])-28,MAX(Calender[Date]))) it evaluates to 7.21 M sales

Here is the PBIX File

 

 

cjayaneththi
Helper I
Helper I

Calendar table is creating the problem. Your measure is based on calendar table so missed matched with sales data dates

Anonymous
Not applicable

@cjayaneththi

 

Hello, 

 

when I am using below formular, it will give me the last 28 days sales, but it will not work by store or sku level.  Any idea how to fix it. 

Last28DaysSales 2 = CALCULATE([TotalSales],DATEADD(SalesDetails[Date],-28,DAY))

Thank you  

Create new date table using for date.

Values=SalesDeails[date] and get rid of calendar date table and link the relationship

This will give you sales for last 28 days but excluding any dates that has no salesCapture.PNG

Anonymous
Not applicable

I tired this. It works. Thank you so much for the help and your time. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.