topic Re: Need Last Week data using DAX in DAX Commands and Tips

Need Last Week data using DAX

Sudharshan1919 — Mon, 13 Jun 2022 13:20:42 GMT

Hi ,

I have a data of showing day wise incidents (ticket). We are maintaining this things in excel sheet. My Requirement is to show the last weeks data (not last 7 days).

Can someone help how do we write the formula to get the last week data. Day begins from Mon to Sun.

Thanks,

Sudharshan

Re: Need Last Week data using DAX

Greg_Deckler — Mon, 13 Jun 2022 13:32:23 GMT

Sudharshan1919 This is easiest if you create a Sequential week column. Then you don't have to worry about years and years having 52 or 53 weeks in them.

Sequential - Microsoft Power BI Community

Re: Need Last Week data using DAX

Anonymous — Thu, 16 Jun 2022 09:01:54 GMT

Hi Sudharshan1919 ,

If you just want to compare data from the same year:

Measure = CALCULATE ( SUM ( 'table'[value] ), FILTER ( ALLSELECTED ( 'table' ), YEAR ( 'table'[date] ) = YEAR ( MAX ( 'table'[date] ) ) && WEEKNUM ( 'table'[date], 2 ) = WEEKNUM ( MAX ( 'table'[date] ), 2 ) ) )

If you just want to compare data across years, you will need to create a year_week column then create a rank column based on it.

year_week = YEAR('table'[date])*100+WEEKNUM('table'[date],2) _rank = RANKX('table','table'[year_week],,ASC,Dense)

Measure = CALCULATE ( SUM ( 'table'[value] ), FILTER ( ALLSELECTED ( 'table' ), 'table'[_rank] = MAX ( 'table'[_rank] ) - 1 ) )

Best Regards,

Jay