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.