Rolling 17 weeks data based on date range

[sreddy47652]

Hi Team,

I need to help on dax query to find out the rooling 17 weeks of data based date selection by user, for example if user select date as 8/5/2024 to 4/8/2024 so total days would be 119 days i need to show the Weeknum for and weeklyinflow based on date selection by user. I have created the below measure but it's not giving proper reults.

VAR selected_date = MAX(Sales'[Created_Date] )
VAR start_date = weeknum(selected_date) -17
VAR end_date = selected_date
VAR Result =
CALCULATE(
DISTINCTCOUNTNOBLANK('sales'[Inflow Count]),
FILTER(
'Sales',
'Sales'[Created_Date] >= start_date &&
'Sales'[Created_Date] <= end_date
)
)
RETURN
Result

[PowerBIDave · Accepted solution]

@sreddy47652

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.

[Anonymous · Accepted solution]

Hi @sreddy47652 ,

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. 

[PowerBIDave · Accepted solution]

@sreddy47652

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.

[amitchandak]

@sreddy47652 , Create a date table, join of date of date table with date of you tables and use columns from date table in measure, visual and slicers

Have these new columns in Date Table, Week Rank is Important in Date/Week Table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
WeekDay = weekday([Date],2)

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

These measures can help

Last 17 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-17 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))