Forum Discussion
Weekly Calculation Based on Date Filter
Hello Experts,
I am new to Power BI and I'm struggling on creating a measurement that calculates how many inspections were done in the week based on what date the user selects in a slicer.
I have 2 tables:
- DataDump Table (where all my entries are)
- Date Week Table (calendar table)
I have a slicer which shows dates from my Date Week Table, the user selects what day they want to see data from, then my visuals pull the data from my DataDump table for that day.
What I need is a measure that calculates how many inspections were or have been done in that week of the day selected.
For example if user selects 01/05/2022, I want a measure that shows inspections done from 01/03/2022-01/09/2022
DataDump Table
Date Week Table
Date Slicer
I have this other calculation where it shows how many inspection done in the date range selected by user. On 01/05/2022 a total of 354 inspections were done
This is the formula I have for the measure above
Here is where I want to show the result. For this selection the result should be: 1607 inspections done for the week of 01/03/22-01/09/22
I just can't figure out how to do the formula to do what I need it to do. Hopefully I explained myself.
I appreciate the help!
Hi ivasgal ,
Make sure the relationship between DataDump Table and Date Week Table is inactive.
You have weeknumber in the date week table so you could create a measure as below:
measure = calculate(counta(DataDump[rows]),filter(allselected(DataDump),weeknum(DataDump[inspections date],2) = selectedvalue(Date Week[week])))
Best Regards,
Jay
2 Replies
ivasgal , Have week rank column in you date/week tbale
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatthen try measures
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 ivasgal ,
Make sure the relationship between DataDump Table and Date Week Table is inactive.
You have weeknumber in the date week table so you could create a measure as below:
measure = calculate(counta(DataDump[rows]),filter(allselected(DataDump),weeknum(DataDump[inspections date],2) = selectedvalue(Date Week[week])))
Best Regards,
Jay
