Measures with Weeknum for past (1,2,3,4,5) weeks

[pferban]

Hi, and happy new year!!

I have 5 measurements to calculate the sum for:

This week
This week -1
This week -2
This week -3
This week -4
This week -5

With the change of year, the formula no longer works. This is the formula used by subtracting the necessary weeks in each measure:

=CALCULATE(SUM(QTY);FILTER(TABLE,WEEKNUM(TABLE.DATE;2)=WEEKNUM(TODAY();2)-1))

WEEKNUM(TODAY();2) = 2

WEEKNUM(TODAY();2)-1= 1

WEEKNUM(TODAY();2)-2=0

...

I need:

WEEKNUM(TODAY();2) -2 = 53

Do you know any solution?
Thank you

[AlB]

Hi @pferban 

The easiest would be to create an additional column on your date table that has the weeknumber across years.  Then you can use that column to find all those weeks by just subtracting 1, 2, etc.  One option:

WeekyNumGlobal =
CALCULATE (
    DISTINCTCOUNT ( DateT[Weeknumber] ),
    DateT[Date] <= EARLIER ( DateT[Date] ),
    ALL ( DateT )
)

This assumes you have a WeekNumber column in your date already. Create it if you do not

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@pferban , Not very clear, refer to my week blogs if they can help

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8