Forum Discussion

Sudharshan1919's avatar
Sudharshan1919
Helper III
4 years ago
Solved

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 d...
Commands and Tips
DAX
dax formula
Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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