Dynamic card to show previous week and date range

[Anonymous]

Hello,

I have a calendar table connected to my data table and i want to have a card visualization which shows the previous week number, the year associated with that week number and the range of dates, such as:

Week #10 2022

7 - 13 March

Here is my attempt but i am not sure how to proceed since i want to link back to my calendar table to get the correct week number and corresponding date range:

Label with Line Breaks = 
VAR Label =
    "Week #" & [PreviousWeekNumber]
        & UNICHAR ( 10 ) & YEAR(TODAY())
    "Dates: " & SelectedDateRange
        & UNICHAR ( 10 ) & SelectedItem
RETURN
    Label

Any help is much appreciated!

[davehus · Accepted solution]

Hi @Anonymous ,

Could you try something like this maybe? Using PowerQuery get the the start date for the previous week

Powerquery measure

DateLastWeek = Date.AddDays(Date.StartOfWeek([DateKey]),-7)

Then use Add Column to get the Week, Year, Week Start/End etc from this column.

DatePriorWeek = "Week #"&Text.From([Week of Year])&" "&Text.From([Year])&" "&Text.Start(Text.From([Start of Week]),2)&" - "&Text.Start(Text.From([End of Week]),2)& " " &Text.From([Month Name])

Delete the Add Columns except the custom measure.

Finally Create A Dax Measure 

Measure = CALCULATE( MAX(DateTable[DatePriorWeek]), FILTER(DateTable,DateTable[DateKey]=MAX(DateTable[DateKey])))

To get below: