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

Help with cumulative sales of the last 5 weeks ( no dates data)

Hi guys,

 

i want to calculate the cumulative sales of the last 5 for each week. I have 2 tables 1 for sales and 1 for week/month because i wil have other tables like for sales 2018,2017,2016 etc. and those tables will be linked to the week/month for comparative analisys.

 

PS. I dont have a date in the data so measures like this one wont help me 😕

 

Last4weeksSales =
CALCULATE (
    [Sum of Sales Measure],
    DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -28, DAY )
)

 

FWC.JPGfwc2.JPG

 

Thanks in advance for your help 🙂 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

Try like this

 

Last4weeksSales =
  _min_week = max([Week Number]) -4
  _max_week = max([Week Number])
CALCULATE (
    [Sum of Sales Measure],
    all([Week Number]), [Week Number] >= _min_week && [Week Number] <=_max_week
)

 

In place of max, you might require maxx. Also, you need to adjust when the week becomes negative.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

 

 

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

Try like this

 

Last4weeksSales =
  _min_week = max([Week Number]) -4
  _max_week = max([Week Number])
CALCULATE (
    [Sum of Sales Measure],
    all([Week Number]), [Week Number] >= _min_week && [Week Number] <=_max_week
)

 

In place of max, you might require maxx. Also, you need to adjust when the week becomes negative.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

 

 

 

Anonymous
Not applicable

Thanks a Lot! it worked !

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.