cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## Need Last Week data using DAX

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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
2 REPLIES 2
Community Support

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

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...