List of dates for previous week in time using intelligent time slicer

[Alex_LevelUp]

Hello -
I'm trying to make a table with values in a column called "Selection" for MTD, YTD, PYTD, PMTD, and WEEKLY. I'm using the concept for intelligent time slicers linked here: (https://youtu.be/AdLDYohLeJc). I'm currently hung up on trying to generate the "Weekly" Selection. I can generate the dates properly for WTD using:

VAR WeekStart =

     CALCULATE(

        TodayDate-WEEKDAY(TodayDate,3),

        FILTER(

            DISTINCT('Dates'[Date]),

            YEAR('Dates'[date])

            =YEAR(TodayDate)

     )

)

and then:

    ADDCOLUMNS(

        CALENDAR(WeekStart, TodayDate),

        "Selection", "Weekly",

        "Sort", 6))

However, the data I would like to filter on this specific request is trailing by 3 weeks. How would I obtain a list of dates using the calendar function for a week of dates that is 3 weeks back? Ex. if the date is 8/22/22, I would like the list of days for the week of 8/1/22-through 8/7/22. 

I have the extended date table available with week offsets, but I can't figure out how to use dax to get the dates I would like to display.

Thanks!

[v-yangliu-msft · Accepted solution]

Hi @Alex_LevelUp ,

According to your description, According to your DAX, I can see that you want to get the date three weeks before TODAY().Right?

(1)You can use the following DAX to replace the italicized part of your error :

VAR WeekStart =

     CALCULATE(

        TodayDate-WEEKDAY(TodayDate,3)

,FILTER(

            DISTINCT('Dates'[Date]),

            YEAR('Dates'[date])

            =YEAR(TodayDate)

     )

)

VAR _pre_three_week_start =

    CALCULATE(

        DATEADD('Dates'[Date], -21, DAY),

        FILTER(

            'Dates','Dates'[Date]=WeekStart

    )

)

VAR _pre_three_week_end =

    CALCULATE(

        DATEADD('Dates'[Date], -15, DAY),

        FILTER(

           'Dates','Dates'[Date]=WeekStart

    )

)

(2)And the [Result] is :

ADDCOLUMNS(
CALENDAR(_pre_three_week_start, _pre_three_week_end),
"Selection", "WeeklyTEST",
"Sort", 7
)

(3)The Result as follows:

If this method can’t  meet your needs, can you provide your Date table for us  so that we can help you better.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Alex_LevelUp]

Perfect! Thank you for your help!

[v-yangliu-msft]

Hi @Alex_LevelUp ,

According to your description, you want to display the date of the third week before by a certain date. Right?

Here are the steps you can follow：

(1)We can create a date table using CALENDAR() based on test table. The two tables do not need to be connected.

Date  =  CALENDAR(FIRSTDATE('test'[Date]),LASTDATE('test'[Date]))

(2)We put the ‘Date’[Date]  in the slice and put the ‘test’[Date] in the Table or Matrix.

(3)Create Measure  “isDisplay”:

isDisplay =
var _week_day= WEEKDAY(SELECTEDVALUE('Date '[Date]),3)
var _week_start= DATEADD( VALUES('Date '[Date]), -_week_day,DAY)
var _pre_three_week_start= DATEADD(_week_start,-21,DAY)
var _pre_three_week_end=DATEADD(_week_start,-15,DAY)
return
  IF( HASONEFILTER('Date '[Date]), IF( MAX('test'[Date]) >=_pre_three_week_start && MAX('test'[Date]) <= _pre_three_week_end ,1,0) ,0)

(4)We can put the “isDisplay” measure in the Table or Matrix visual filter. After it ,we configure the filter and click “Apply filter”.

(5)Result: When you select a date in the slice ,we can see the date of the third week before the date we selected.

If this method can't meet your requirement, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

If you need pbix, please click here.

case_02.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Alex_LevelUp]

This seems really close, particulary to define the previous three week start and end date by a variable.

Below is how I'm structuring my custom table. It is simply using fields from a date table.
Once my table is built, I would use a slicer where I filter on the "selection" column in my custom table. I tried to use your variables as they were set up, but I was getting a ciruclar dependency error, so I used the FILTER & DISTINCT solution that worked on my measures. Now I'm getting a "DATEADD" must specify a column error.

The measures in question that you provided are in italics. How do I adjust those to work with the current format? The other measures work.

Weekly/MTD/YTD/PYTD Selection =

VAR TodayDate = TODAY()

VAR YearStart =

    CALCULATE

        (STARTOFYEAR('Dates'[Date]),

        FILTER(DISTINCT('Dates'[Date]),

        YEAR(Dates[Date]) = YEAR(TodayDate)

    )

)

VAR MonthStart =

    CALCULATE

    (STARTOFMONTH(Dates[Date]),

    FILTER(

        DISTINCT('Dates'[Date]),

        YEAR('Dates'[Date]) = YEAR (TodayDate)

        &&MONTH ('Dates'[Date]) = MONTH(TodayDate)

    )

)

VAR PYTodayDate =ble in question are in italics

    DATE(YEAR(TodayDate)-1, MONTH(TodayDate), DAY(TodayDate))

VAR PYMonthStart =

    CALCULATE(

        DATEADD(STARTOFMONTH(Dates[Date]), -1, YEAR),

        FILTER(

            DISTINCT('Dates'[Date]),

            YEAR('Dates'[Date]) -1

            = YEAR(PYTodayDate)

            &&MONTH('Dates'[Date]) = MONTH(TodayDate)

    )

 )

VAR PYstart =

    CALCULATE(

        DATEADD(STARTOFYEAR(Dates[Date]), -1, YEAR),

        FILTER(

            DISTINCT('Dates'[Date]),

            YEAR('Dates'[Date]) -1

            =YEAR(PYTodayDate)

     )

)

VAR WeekStart =

     CALCULATE(

        TodayDate-WEEKDAY(TodayDate,3),

        FILTER(

            DISTINCT('Dates'[Date]),

            YEAR('Dates'[date])

            =YEAR(TodayDate)

     )

)

var _week_day=

    CALCULATE(

        WEEKDAY(SELECTEDVALUE('Dates'[Date]),3),

        FILTER(

            DISTINCT(Dates[Date]),

            YEAR('Dates'[Date])

            =YEAR(TodayDate)

    )

)

var _week_start=

    CALCULATE(

        DATEADD( VALUES('Dates'[Date]), -_week_day,DAY),

        FILTER(

            DISTINCT(Dates[Date]),

            YEAR('Dates'[Date])

            = YEAR(TodayDate)

    )

)

VAR _pre_three_week_start =

    CALCULATE(

        DATEADD(_week_start, -21, DAY),

        FILTER(

            DISTINCT(Dates[Date]),

            DAY(Dates[Date])

    )

)

VAR _pre_three_week_end =

    CALCULATE(

        DATEADD(_week_start, -15, DAY),

        FILTER(

            DISTINCT(Dates[Date]),

            DAY(Dates[Date])

    )

)

VAR Result =

UNION (

    ADDCOLUMNS(

        CALENDAR(MIN(Dates[Date]), MAX(Dates[Date]) ),

        "Selection", "All",

        "Sort ", 1

     ),

    ADDCOLUMNS(

        CALENDAR(YearStart, TodayDate),

        "Selection", "YTD",

        "Sort", 4

    ),

    ADDCOLUMNS(

        CALENDAR(MonthStart,TodayDate),

        "Selection", "MTD",

        "Sort", 2

    ),

    ADDCOLUMNS(

        CALENDAR(PYStart, PyTodayDate),

        "Selection", "PYTD",

        "Sort", 5

    ),

    ADDCOLUMNS(

        CALENDAR(PYMonthStart, PYTodayDate),

        "Selection", "PYMTD",

        "Sort", 3

    ),

    ADDCOLUMNS(

        CALENDAR(WeekStart, TodayDate),

        "Selection", "Weekly",

        "Sort", 6

    ),

    ADDCOLUMNS(

        CALENDAR(_pre_three_week_start,_pre_three_week_end),

        "Selection", "WeeklyTEST",

        "Sort", 7

    )

RETURN

Result

[Greg_Deckler]

@Alex_LevelUp Sequential makes things like that easy: https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231?search-action-id=25265...