Newbie struggling to return the week number for last week from a calendar table

[LauraChou]

Hi all,

I'm new to Power BI (from Qlik) and battling with DAX logic a little, it'll click for me eventually I'm sure.

All I want to do is return last week's week number from the attached calendar table.  If the relative week is -1, then return the value populated in the "Week of Year" column.  In the attached calendar the result should be 29.  I then want to use that measure to populate in a text box so it dynamically references the week of the report.

I think I need the filter fuction but I don't think I need an aggregation to go around it, IF only gives me the option of populating one of the measures in my calendar so I know that's not right.  I've tried the LOOKUPVALUE function but I think that's for searching values between tables (as in VLOOKUP)....

Any help would be appreciated. 

Date	Year	Quarter	Month Num	Month	Day Name	Week of Year	Start of Month	StartOfWeek	StartOfCurrentWeek	Weeksago	Monthsago	End of Month	Days in month	Mondays in month	Year Week	IsCurrentWeek	CurrentWeekDate	CurrentWeekOfYear	RelativeWeek
01/01/2024	2024	Qtr 1	1	Jan	Mon	1	01/01/2024	01/01/2024	22/07/2024	29	6	31/01/2024	31	5	202401	FALSE	22/07/2024	30	-29
08/01/2024	2024	Qtr 1	1	Jan	Mon	2	01/01/2024	08/01/2024	22/07/2024	28	6	31/01/2024	31	5	202402	FALSE	22/07/2024	30	-28
15/01/2024	2024	Qtr 1	1	Jan	Mon	3	01/01/2024	15/01/2024	22/07/2024	27	6	31/01/2024	31	5	202403	FALSE	22/07/2024	30	-27
22/01/2024	2024	Qtr 1	1	Jan	Mon	4	01/01/2024	22/01/2024	22/07/2024	26	6	31/01/2024	31	5	202404	FALSE	22/07/2024	30	-26
29/01/2024	2024	Qtr 1	1	Jan	Mon	5	01/01/2024	29/01/2024	22/07/2024	25	6	31/01/2024	31	5	202405	FALSE	22/07/2024	30	-25
05/02/2024	2024	Qtr 1	2	Feb	Mon	6	01/02/2024	05/02/2024	22/07/2024	24	5	29/02/2024	29	4	202406	FALSE	22/07/2024	30	-24
12/02/2024	2024	Qtr 1	2	Feb	Mon	7	01/02/2024	12/02/2024	22/07/2024	23	5	29/02/2024	29	4	202407	FALSE	22/07/2024	30	-23
19/02/2024	2024	Qtr 1	2	Feb	Mon	8	01/02/2024	19/02/2024	22/07/2024	22	5	29/02/2024	29	4	202408	FALSE	22/07/2024	30	-22
26/02/2024	2024	Qtr 1	2	Feb	Mon	9	01/02/2024	26/02/2024	22/07/2024	21	5	29/02/2024	29	4	202409	FALSE	22/07/2024	30	-21
04/03/2024	2024	Qtr 1	3	Mar	Mon	10	01/03/2024	04/03/2024	22/07/2024	20	4	31/03/2024	31	4	202410	FALSE	22/07/2024	30	-20
11/03/2024	2024	Qtr 1	3	Mar	Mon	11	01/03/2024	11/03/2024	22/07/2024	19	4	31/03/2024	31	4	202411	FALSE	22/07/2024	30	-19
18/03/2024	2024	Qtr 1	3	Mar	Mon	12	01/03/2024	18/03/2024	22/07/2024	18	4	31/03/2024	31	4	202412	FALSE	22/07/2024	30	-18
25/03/2024	2024	Qtr 1	3	Mar	Mon	13	01/03/2024	25/03/2024	22/07/2024	17	4	31/03/2024	31	4	202413	FALSE	22/07/2024	30	-17
01/04/2024	2024	Qtr 2	4	Apr	Mon	14	01/04/2024	01/04/2024	22/07/2024	16	3	30/04/2024	30	5	202414	FALSE	22/07/2024	30	-16
08/04/2024	2024	Qtr 2	4	Apr	Mon	15	01/04/2024	08/04/2024	22/07/2024	15	3	30/04/2024	30	5	202415	FALSE	22/07/2024	30	-15

[LauraChou]

That worked a charm!  Thank you!

[Greg_Deckler · Accepted solution]

@LauraChou I would think maybe something like this:

Measure = 
  VAR __Today = TODAY()
  VAR __CurrentWeek = MINX( FILTER( ALLSELECTED( 'Table' ), [StartOfWeek] >= __Today ), [Week of Year] )
  VAR __Result = __CurrentWeek - 1
RETURN
  __Result