Setting up a slicer to view tasks completed within the last 7 days, 14 days and all time

[NB689]

Hello everyone,

I would like to have the ability to view a table of data shown below based on the completion date within the last 7 days, 14 days, or All Time. I have been able to do this in the past for the range of MTD/QTD/YTD by using this walkthrough video: https://www.youtube.com/watch?v=AdLDYohLeJc 

Here is an example of what I have so far:

But my issue is that instead of basing the buckets off of MTD/QTD/YTD, I would like to set this up based off of the ranges I listed above (1 week/2weeks/all).

I have looked at examples of other solutions using the SWITCH functionality and a disconnected table, but my lack of DAX knowledge is preventing me from getting that solution to work.

This is what my current data looks like in the table I created that provides the MTD/QTD/YTD buckets. I have relationships connecting this to my main data set and a seperate date range:

What I would want to change it to would be something like this assuming today is 2/18/2022:

2/9/2022: Last 2 Weeks

2/10/2022: Last 2 Weeks

2/11/2022: Last 1 Week

2/12/2022: Last 1 Week

Here is the DAX I am using to create the table providing the buckets: 

MTD/QTD/YTD Selection = 
VAR TodayDate = TODAY()
VAR YearStart = CALCULATE(STARTOFYEAR('Worklogs'[Time Entry Date]), YEAR('Worklogs'[Time Entry Date]) = YEAR(TodayDate)  )
VAR QuarterStart = CALCULATE(STARTOFQUARTER('Worklogs'[Time Entry Date]), YEAR('Worklogs'[Time Entry Date]) = YEAR(TodayDate), QUARTER('Worklogs'[Time Entry Date]) = QUARTER(TodayDate)  )
VAR MonthStart = CALCULATE(STARTOFMONTH('Worklogs'[Time Entry Date]), YEAR('Worklogs'[Time Entry Date]) = YEAR(TodayDate), MONTH('Worklogs'[Time Entry Date]) = MONTH(TodayDate)  )
VAR Result = 
UNION (
        ADDCOLUMNS (
            CALENDAR ( YearStart, TodayDate  ),
            "Selection", "YTD"
        ),
        ADDCOLUMNS (
            CALENDAR ( QuarterStart, TodayDate  ),
            "Selection", "QTD"
        ),
            ADDCOLUMNS (
            CALENDAR ( MonthStart, TodayDate  ),
            "Selection", "MTD"
        )
)
RETURN
Result

Thank you

[v-rongtiep-msft]

Hi @NB689 ,

Please have a try.

Create a measure.

Today =
VAR today_ =
    TODAY ()
VAR _weeknum =
    ( WEEKNUM ( MAX ( 'table'[Date] ), 1 ) )
VAR _difference =
    WEEKNUM ( TODAY (), 1 )
VAR a_answer = _difference - _weeknum
RETURN
    IF (
        a_answer = 0,
        "this week",
        IF ( a_answer = 1, "Last 1 Week", IF ( a_answer = 2, "Last 2 Week", a_answer ) )
    )

Then you can edit your MTD/QTD/YTD Selection formula.

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[NB689 · Accepted solution]

That looks great! I ended up using this but it looks like your dax is very similar. Thanks for your help.

Slicer Date Periods = 
UNION(
ADDCOLUMNS(
    CALENDAR(TODAY()-7,TODAY()),
    "Type", "Last Week"
    ),
ADDCOLUMNS(
    CALENDAR(TODAY()-14,TODAY()),
    "Type", "Last 2 Weeks"
    ),
ADDCOLUMNS(
   CALENDAR(MIN('Date 2'[Date]),MAX('Date 2'[Date])),
    "Type", "All Time"
    )
)