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
Likhitha
Helper IV
Helper IV

Doubt in calculating sum of last 6 weeks sum

Hello All,

I'm having 52 weeks data in 2019 and 3 weeks data in 2020.I want to calculate last 6 weeks sales sum.

if i select 1st week in 2020 i need need to do sum from 1-2020 to 47-2019.

How to achieve that? 

2 ACCEPTED SOLUTIONS

Hi @Likhitha ,

If you are using a date table, this is doable. If not, then it is a lot harder. I am going to assume you do have a datetable. We are going to need a yearweek column in the format yyyyww (eg 202001 for week 1 in 2020). Then we are going to add a column in your date table that ranks all yearweek in a densly matter. Like this:

image.png 

The formula of the datetable and the added column are these:

DateTable = ADDCOLUMNS(CALENDAR("01/01/2017", "31/12/2020"), 
     "yearweek", 
     CONCATENATE(FORMAT(YEAR([Date]), "####"), FORMAT(WEEKNUM([Date]), "0#")))
RankedYearWeek = RANKX(DateTable, DateTable[yearweek], , ASC, Dense)

Now you need to link your date table to the date column in your fact table. Depending on your datamodel and columns, you can calculate what dates are in the past 6 weeks by looking at the current RankedYearWeek and subtract 6. This is quite an advanced solution that might require you to rethink your datamodel but I would suit your needs.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Hi @Likhitha ,

 

check this out.

PBIX

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

13 REPLIES 13
dax
Community Support
Community Support

Hi @Likhitha , 

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

amitchandak
Super User
Super User

Try like

Rolling 6 week = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6*7,DAY))  

 

This should give rolling 6 week for each date.

 

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 -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

Hi @Likhitha ,

If you are using a date table, this is doable. If not, then it is a lot harder. I am going to assume you do have a datetable. We are going to need a yearweek column in the format yyyyww (eg 202001 for week 1 in 2020). Then we are going to add a column in your date table that ranks all yearweek in a densly matter. Like this:

image.png 

The formula of the datetable and the added column are these:

DateTable = ADDCOLUMNS(CALENDAR("01/01/2017", "31/12/2020"), 
     "yearweek", 
     CONCATENATE(FORMAT(YEAR([Date]), "####"), FORMAT(WEEKNUM([Date]), "0#")))
RankedYearWeek = RANKX(DateTable, DateTable[yearweek], , ASC, Dense)

Now you need to link your date table to the date column in your fact table. Depending on your datamodel and columns, you can calculate what dates are in the past 6 weeks by looking at the current RankedYearWeek and subtract 6. This is quite an advanced solution that might require you to rethink your datamodel but I would suit your needs.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@JarroVGIT  If possible can you attach pbix file for that.I'm getting value for selected week only

Hi @Likhitha ,

 

check this out.

PBIX

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


@mwegener  I don't have continuous weeks to apply that logic.I attached a sample format in one reply you can check that for reference

Hi @Likhitha ,

 

check this out.

PBIX

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


Anonymous
Not applicable

Hi @mwegener ,

 

I couldn't able to see the PBIX file which you shared, could you please share that file the way "@dax" shared the file in that same loop.

 

Because the file which was shared by @dax that i can able to open but not able to open yours pbix.

 

Thanks

 

 

@mwegener  Thanks  a lot ...It worked...Thank You very very much 🙂

There is nothing in my pbix but the datacalendar that I showed you? The first code is a calculated table and the second is a calculated column added to that table. It makes more sense if you add a ppbix if you are struggling somewhere so I can have a look?




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@JarroVGIT  Here attaching sample excel data in which i'm having week and sales.week is in text 

For 2020 I'm having 4 weeks data and in 2019 i'm having data upto 50 th week.

If i select 4th week in 2020 and sales sum should be 4-2020 to 49-2019 and i'm projecting sales sum against product and week is my slicer.

 

 

WeekSales
40.2019100
41.201980
42.201960
43.201940
44.201910
45.2019150
46.2019156
47.2019200
48.201970
49.201929
50.201934
01.202076
02.202056
03.2020300
04.2020150

@amitchandak  I'm having week column in the format(weeknumber-year) and i don't have date format

JarroVGIT
Resident Rockstar
Resident Rockstar

You will need to give a lot more information for us to help you. What does the data look like? What have you tried? What do you expect the outcome to be? (e.g. just one value and hard coded weeks? Or a matrix with all weeks and corresponding 6 week sales sum?) Are you using a date table?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.