Forum Discussion
Current Week Number, for current year
Hello
I've been trying to work out the latest week number for the current financial year. My data has 'week number' in one column, and the period (year) in another - The years are described as FY2020-21 and FY2021-22.
I've been using (below) to find the current week number, but now as we have new data for the new year i am struggling to work up how to factor in the 'year' into the query ... Any tips would be great 🙂
Current Week Count =
VAR CurrWeek =
MAX(DATA[Week No])
RETURN
CALCULATE(
SUM(DATA[Filter]),
FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek),
VALUES(DATA[Tower]),
VALUES(DATA[priority])
)
streep1 , Try like this
Current Week Count =
VAR CurrYear =
MAXX(allselected(Data),DATA[Year])
VAR CurrWeek =
MAXX(Filter(allselected(Data), DATA[Year] =CurrYear),DATA[Week No])
RETURN
CALCULATE(
SUM(DATA[Filter]),
FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek && DATA[Year] =CurrYear),
VALUES(DATA[Tower]),
VALUES(DATA[priority])
)Or you can have year week column and take a max for it
Year week = [Year]*100 +[Week]
3 Replies
streep1 , Try like this
Current Week Count =
VAR CurrYear =
MAXX(allselected(Data),DATA[Year])
VAR CurrWeek =
MAXX(Filter(allselected(Data), DATA[Year] =CurrYear),DATA[Week No])
RETURN
CALCULATE(
SUM(DATA[Filter]),
FILTER(ALLSELECTED(DATA),[Week No]=CurrWeek && DATA[Year] =CurrYear),
VALUES(DATA[Tower]),
VALUES(DATA[priority])
)Or you can have year week column and take a max for it
Year week = [Year]*100 +[Week]
Thanks, for the fast reply that worked a treat!
Hi all, I have a column in the calendertable which says which week it is; how can I retrieve this weeknumber as a number in power bi with a measure?
For example, this day, the corresponding weeknumber should be 40. I think it should be very easy... but I can't get it... Thanks in advance for the help.
