Forum Discussion
Rolling 17 weeks data based on date range
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
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 ResultTo 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 MondayWeekly 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.
3 Replies
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.
Anonymous , 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 TableWeek Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format
These measures can helpLast 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))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 ResultTo 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 MondayWeekly 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.
