Dynamic anchor date to always select max date

[reboundgt9]

I have a report that I run on a weekly basis and am trying to automate as much of the process as possible. Ideally we would like to be able to simply refresh the data instead of promoting new versions of the report into the production environment each week.

My current process is that I append a claims file every Wednesday morning that includes claims paid between the prior Wednesday through Tuesday. In this scenario, I've made a custom date table which also includes my week count since we run on a Wednesday-Tuesday week basis and other weeekly data functions (ie. WEEKNUM) have not worked for this scenario.

My relative date range filer is set to the "Last"/ Week Count / "Weeks." My issue is that I need to update the anchor date of my relative date filter to the last date of the paid claims, or the previous day/Tuesday, each time I update the report. For example, since today is 12/15/2021, I ran a paid claims query between 12/8/2021 and 12/14/2021 and append the data on 12/15/2021. In order for the measures to flow into my dashboard correctly,  I've needed to update the Date Range > Anchor Date within the Visualizations pane to the Tuesday date each and every week.

My question is...is there a way to dynamically set/update the anchor date each week without manual intervention?

[v-rzhou-msft · Accepted solution]

Hi @reboundgt9 ,

I think you want to show data between last Wednesday to Tuesday. For example, today = 2021/12/21(Tuesday), you will update your data between 2021/12/15 to 2021/12/21 on 2021/12/22 (Wednesday). So today you still want to get data between 2021/12/08 to 2021/12/14. Here I suggest you to create a weekgroup column in your date table and then create a filter measure to filter your visual.

My Date table:

Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2021, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Day", DAY ( [Date] ),
    "DayName", FORMAT ( [Date], "DDDD" )
)

Weekgroup Column:

WeekGroup =
CALCULATE (
    COUNT ( 'Date'[DayName] ),
    FILTER (
        'Date',
        'Date'[Date] <= EARLIER ( 'Date'[Date] )
            && 'Date'[DayName] = "Wednesday"
    )
)

Measure:

Filter =
VAR _WeekGroup =
    CALCULATE ( SUM ( 'Date'[WeekGroup] ), 'Date'[Date] = TODAY () )
RETURN
    IF ( SUM ( 'Date'[WeekGroup] ) = _WeekGroup - 1, 1, 0 )

Add this measure into filter field in your visual and set it to show items when the value is 1. Result is as below.

Best Regards,
Rico Zhou

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

[parry2k]

@reboundgt9 you can control this by putting the logic in your measure so that it includes the dates you are interested in and you have better control on it.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[reboundgt9]

Thank you for the reply! I was trying to go the easier route and update the relative filter versus updating all of the measures.

My only concern in trying to work through the measures is that I had to define my own "week" time frame as we run on a Wednesday - Tuesday week. Using any other weekly function does not allow you to customize the week to those date parameters.