Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
45 | |
28 | |
21 | |
11 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |