cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors