Display last 6 week data for Year change

[Anonymous]

Hi ,

I need to display last 6 weeks data but the previous week has week 53 of 2020 and week 1 2021 , it is not showing the data correctly. Can you please help me to get the last 6 weeks data despite of change of year ?

Some screenshots attached for reference :

The highlighted bar showing the data till 31/12/2020 , but the tables has values for 01/01/21 & 02/01/21 too.

[v-rzhou-msft]

Hi @Anonymous 

I think your problem is that when you use weeknum function, it will restart weeknum by 1 in a new year.

So you can't get the value in 2021/1/1 and 2021/1/2 which should in weeknum 53.

I think you may need an Accumulate_Week_Num to achieve your goal.

My Sample Table:

WEKK_NBR and YEAR are calcualted columns.

WEEK_NBR = WEEKNUM(Append1[CHAT_DATE],1) 

YEAR = YEAR(Append1[CHAT_DATE])

Accumulate_Week_Num:

Accumulate_Week_Num = 
IF (
    RANKX ( Append1, Append1[YEAR],, ASC, DENSE ) <> 1,
    SUMX (
        SUMMARIZE (
            FILTER ( Append1, Append1[YEAR] < EARLIER ( Append1[YEAR] ) ),
            "Maxweeknum-1",
                MAXX (
                    FILTER ( Append1, Append1[YEAR] < EARLIER ( Append1[YEAR] ) ),
                    Append1[WEEK_NBR]
                ) - 1
        ),
        [Maxweeknum-1]
    ) + Append1[WEEK_NBR],
    WEEKNUM ( Append1[CHAT_DATE], 1 )
)

Result is as below:

You can update your Last 6 weeks column as below.

Last 6 weeks = 
VAR _NowWeekNum =
    CALCULATE (
        MAX ( Append1[Accumulate_Week_Num] ),
        FILTER (
            Append1,
            FORMAT ( Append1[CHAT_DATE], "yyyymmdd" ) = FORMAT ( NOW (), "yyyymmdd" )
        )
    )
RETURN
    IF (
        Append1[Accumulate_Week_Num] >= _NowWeekNum - 6
            && Append1[Accumulate_Week_Num] < _NowWeekNum,
        1,
        0
    )

Result is as below.

You can download the pbix file from this link: Display last 6 week data for Year change

Best Regards,

Rico Zhou

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

[Anonymous]

Hi @v-rzhou-msft  ,

Thank you for the efforts !!!

 I tried your solution , still after 2 weeks .i.e. 54,53. It showing me some random numbers. And also last 6 weeks column also not giving me any results.Can you help to get that correct ?

[v-rzhou-msft]

Hi @Anonymous 

I think you want to build a calculated column to show 1, if the data in last 6 weeks.

And add last 6 weeks column into the filter and set it to show items when value =1.

Do your last 6 week means that the last 6 weeks before the latest week?

Will you update the latest week? For example, if you update your Chat Date to 2021/1/13, your last 6 weeks will be from Dec06 to Jan 09.

And your question is that in the next year, the weeknum will start from 1 again, not continuous from 53.

It seems that 105 = 53+52, the result should be 53, there may be something wrong in your Accumulate_Week_Num. 

In my sample it works well. You can try to build a Year column and then build Accumulate_Week_Num like mine.

Could you provide me a sample file without sensitive data and give me screenshot to show your requirement?

I need to know your calculate logic and the range of your date.

Best Regards,

Rico Zhou

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

[Anonymous]

@v-rzhou-msft ,

How Can I share my file here ?

Can you give me your emaill address, I will send you there.

[v-rzhou-msft]

Hi @Anonymous 

You can share your pbix file by your Onedrive for Business.

Best Regards,

Rico Zhou

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

[Anonymous]

@v-rzhou-msft ,

Sample file  please check.

[v-rzhou-msft]

Hi @Anonymous 

I don't have access to your file, please change your share setting in your Onedrive for Business.

Best Regards,

Rico Zhou

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

[Anonymous]

@v-rzhou-msft ,

I cannot change the settings on one drive as that is my office login. But I did uploaded the same on Google drive. 

Here is the link : https://drive.google.com/file/d/1Mt7B893mzw76UmxNNR63Zkb4sb0DvRGE/view?usp=drivesdk 

[Anonymous]

Any update ...?

[v-rzhou-msft]

Hi @Anonymous 

I update my calcualted column in your sample.

Accumulate_Week_Num2 will show you continuous weeknum start from 45(2019/11/03) to 106(2021/01/09). 

Last 6 Weeks will show 1 if Max(Accumulate_Week_Num2)106-6<Accumulate_Week_Num2 <=Max(Accumulate_Week_Num2)106.

Accumulate_Week_Num2 = 
IF (
    RANKX ( Append1, Append1[YEAR],, ASC, DENSE ) <> 1,
    SUMX (
        SUMMARIZE (
            FILTER ( Append1, Append1[YEAR] < EARLIER ( Append1[YEAR] ) ),
            Append1[Year],
            "Maxweeknum-1",
                MAXX (
                    FILTER ( Append1, Append1[YEAR] = EARLIER ( Append1[YEAR] )),
                    Append1[WEEK_NBR]
                ) - 1
        ),
        [Maxweeknum-1]
    ) + Append1[WEEK_NBR],
    WEEKNUM ( Append1[CHAT_DATE], 1 )
)

Last 6 weeks = 
VAR _MaxWeekNum =
MAX(Append1[Accumulate_Week_Num2])
RETURN
    IF (
        Append1[Accumulate_Week_Num2] > _MaxWeekNum - 6
            && Append1[Accumulate_Week_Num2] <= _MaxWeekNum,
        1,
        0
    )

You can download the pbix file from this link: Display last 6 week data for Year change

Best Regards,

Rico Zhou

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

[JuanBolas]

Hello @v-rzhou-msft.  I tried your solution starting 9/30/2020 and am getting strange results.  I couldn't access your PBIX to check if I was doing something wrong.

[Anonymous · Accepted solution]

Hi @v-rzhou-msft ,

Yeah I got the results...Thank you so much for the help !!!!

[Anonymous]

Hi @littlemojopuppy  , @Fowmy 

Basically I have below columns and the week starts on sunday. I used to get previous weeks data by if(sheet[WEEK_NBR]=weeknum(now())-1,1,if(sheet[WEEK_NBR]=weeknum(now())-2,1, and so on till 6 weeks  but now due to year change that calculation won't work here.

Any help would  be greatly appreciated.

[Fowmy]

@Anonymous 

Your Week Display has to be modified as follows, it will table Sunday to Saturday regardless of year change:

Week Display = 
FORMAT( Table[Date] - WEEKDAY(Table[Date],11) , "MMM DD") & " - " &
FORMAT( Table[Date] - WEEKDAY(Table[Date],11) + 6, "MMM DD")

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn 

[Anonymous]

Any Update ?

[littlemojopuppy]

Hi.  Could you paste some actual data into a table so I can work with it?  Can't do much with a picture.

[Fowmy]

@Anonymous 

It is difficult to tell without knowing the underlying DAX formula and how the Week span is created in your calendar table. If you could share a sample PBIX file, it will help.

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[littlemojopuppy]

Can you include some sample data and the DAX for the measures you're trying?

[Anonymous]

Below DAX function , I am using :

WEEK_NBR = WEEKNUM(Append1[CHAT_DATE],1) 

Start_of_Week= FORMAT(Append1[CHAT_DATE]-WEEKDAY(Append1[CHAT_DATE],1)+1,"MMM DD")

Week Display = FORMAT(Append1[Start_Date],"MMM DD")& " - " &FORMAT(Append1[Start_Date] + 6,"MMM DD")

Last 6 weeks =

if(Append1[WEEK_NBR]=WEEKNUM(NOW())-1,1,IF(Append1[WEEK_NBR] = WEEKNUM(NOW())-2 ,1,IF(Append1[WEEK_NBR]= WEEKNUM(NOW())-3,1,IF(Append1[WEEK_NBR] = WEEKNUM(NOW())-4 ,1,IF(Append1[WEEK_NBR]= WEEKNUM(NOW())-5,1,IF(Append1[WEEK_NBR]= WEEKNUM(NOW())-6,1,0))))))

I display the last 6 weeks chat_answered data week wise, week starting from sunday.

[Anonymous]

@Fowmy  ,

you did not got my point , weeek display has no issue. I am stuck at showing last 6 weeks data , earlier I was using above function Last 6 weeks , but now due to year change that is not working .

The Week Dec 27 - Jan 2 has 2 weeks - Week 53 of 2020 and Week 1 of 2021. Can you please help me to get last 6 weeks chat_answered ?