Forum Discussion

ivasgal's avatar
ivasgal
New Member
4 years ago
Solved

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...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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