Rolling 17 weeks data based on date range

Anonymous

Your measure won't work because the expression weeknum(selected_date) - 17 returns an integer (whole number) value and not a date.

If you modify your code to replace

VAR start_date = weeknum(selected_date) - 17

with

VAR start_date = selected_date - 119

you will return a date value for your start date.

Hope that helps.

If this answers your question, please mark as a solution so others can find.

Hi Anonymous ,

First of all thanks to amitchandak  and PowerBIDave  for their quick replies. I would like to make some additions:

This measure calculates the start date as 119 days before the selected end date, ensuring it covers the 17-week period. It then counts the distinct inflow counts within this date range.

VAR selected_start_date = MIN(Sales'[Created_Date])
VAR selected_end_date = MAX(Sales'[Created_Date])
VAR start_date = selected_end_date - 119 // 17 weeks * 7 days = 119 days
VAR Result =
CALCULATE(
    DISTINCTCOUNTNOBLANK('Sales'[Inflow Count]),
    FILTER(
        'Sales',
        'Sales'[Created_Date] >= start_date &&
        'Sales'[Created_Date] <= selected_end_date
    )
)
RETURN
Result

To display the week number and weekly inflow, you can create additional measures:

Week Number Measure:

Week Number = WEEKNUM('Sales'[Created_Date], 2) // 2 for starting the week on Monday

Weekly Inflow Measure:

Weekly Inflow = 
VAR selected_end_date = MAX(Sales'[Created_Date])
VAR start_date = selected_end_date - 119
RETURN
CALCULATE(
    DISTINCTCOUNTNOBLANK('Sales'[Inflow Count]),
    FILTER(
        'Sales',
        'Sales'[Created_Date] >= start_date &&
        'Sales'[Created_Date] <= selected_end_date
    )
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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