Doubt in calculating sum of last 6 weeks sum

[Likhitha]

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? 

[JarroVGIT · Accepted solution]

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:

 

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.

[dax]

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]

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

[JarroVGIT · Accepted solution]

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:

 

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.

[Likhitha]

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

[mwegener]

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.

[Likhitha]

@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

[mwegener · Accepted solution]

Hi @Likhitha ,

check this out.

PBIX

[Anonymous]

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

[Likhitha]

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

[JarroVGIT]

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?

[Likhitha]

@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.

Week	Sales
40.2019	100
41.2019	80
42.2019	60
43.2019	40
44.2019	10
45.2019	150
46.2019	156
47.2019	200
48.2019	70
49.2019	29
50.2019	34
01.2020	76
02.2020	56
03.2020	300
04.2020	150

[Likhitha]

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

[JarroVGIT]

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?