cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sander1401
Helper I
Helper I

Last week calculation

Hi all,
 
I have a problem with calculating the result of last week.
A collegue of mine wrote the dax stated below, and it worked. Unfortunately not when we started the new year.
Then it looks for week 0 (week 1 minus 1), and it obviously can't find it.
My idea would be that I need to adjust this VAR:
VAR MaxWeekNumber = CALCULATE(MAX(Datetabel[Weeknr])-1, ALL(Datetabel))

In this VAR I need to calculate the max weeknr of last year, so for example:
MAX(Datetabel[Weeknr]) where YEAR(today())-1

But how can I write this in DAX??
I hope everything is clear and someone can help me out on this.
thanks in advance!
 
Base internet - last week =
VAR CurrentWeek = SELECTEDVALUE(Datetabel[Weeknr])
VAR CurrentYear = SELECTEDVALUE(Datetabel[Year])
VAR MaxWeekNumber = CALCULATE(MAX(Datetabel[Weeknr])-1, ALL(Datetabel))
RETURN

 

SUMX(
FILTER( ALL(Datetabel),
IF( CurrentWeek = 1,
Datetabel[Weeknr] = MaxWeekNumber && Datetabel[Year] = CurrentYear - 1,
Datetabel[Weeknr] = CurrentWeek - 1 && Datetabel[Year] = CurrentYear )),
[Base internet - week]
)
1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@Sander1401 I would recommend implementing something like Sequential and then this is trivial. Can also do something similar in PQ. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

@Sander1401 Make sure you are using the right return type for WEEKNUM:

WEEKNUM function (DAX) - DAX | Microsoft Docs


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I forgot to change one VAR, my mistake, it works now! Thanks again!

Greg_Deckler
Super User
Super User

@Sander1401 I would recommend implementing something like Sequential and then this is trivial. Can also do something similar in PQ. https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@Greg_DecklerGood suggestion, thanks for that. I only are having troubles with the iso weeks/year. So the first 2 days of 2022 (1 january and 2 january 2022) are in Iso week 52-2021. But unfortunately the column calculation doesn't recognize this...
How can I solve this? Do you have an idea?

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.