Weekly Calculation Based on Date Filter

[ivasgal]

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!

[Anonymous · Accepted solution]

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

[amitchandak]

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

then 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))