Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
reboundgt9
Frequent Visitor

Dynamic anchor date to always select max date

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?

1 ACCEPTED SOLUTION
v-rzhou-msft
Community Support
Community Support

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.

1.png

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.

 

View solution in original post

3 REPLIES 3
v-rzhou-msft
Community Support
Community Support

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.

1.png

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
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.