Rolling 7 days represented as 1 in Date table

[mloc1990]

Hi,

Can someone help with the following? I'm trying to create 3  rolling columns in the Date table that each display as 1. These are used in a later calculation for volume. Currently what I was able to create are the 3 columns below but they are static

I'm trying to make these rolling numbers. How would I do this?

Thanks

Previous Week = IF(

    AND(

    VALUE('Date'[WeekNum])=WEEKNUM(TODAY(),2)-1,

    'Date'[Year Reference] = YEAR(TODAY())),

    1)

This Week = IF(

    AND(

    VALUE('Date'[WeekNum])=WEEKNUM(TODAY(),2),

    'Date'[Year Reference] = YEAR(TODAY())),

    1)

2 Weeks ago = IF(

AND(

VALUE('Date'[WeekNum])=WEEKNUM(TODAY(),2)-2,

'Date'[Year Reference] = YEAR(TODAY())),

1)

[v-easonf-msft]

Hi , @mloc1990 

Could you please tell me whether your problem has been solved?
If it is,  please mark the helpful replies or add your reply as Answered to close this thread.

Best Regards,
Community Support Team _ Eason

[mloc1990]

Hi @v-easonf-msft 

Thanks very much for your hep!

In the end I used the folllowing measures in the Date table and the appear to be working fine.

I was also expieriencing another issue where my local regional settings were different to my PBI settings and this was causing formulas not to render correctly.

This Week = IF( DATEDIFF( 'Date'[Date] , TODAY() , DAY ) <= 7 , 1 , 0 )

Previous Week = IF( AND( DATEDIFF( 'Date'[Date] , TODAY() , DAY ) > 7 , DATEDIFF( 'Date'[Date] , TODAY() , DAY ) <=14 ) , 1 , 0 )

[v-easonf-msft]

Hi , @mloc1990 

If  you just want to change your local region,you only need to select  file ->options and setting options ->Regional Settings to change your setting

If  you want to change date local ,please right click the date column in query editor and select change type->using locate  to change the date type with your locale.

If you haven't solved your problem, please feel free to ask.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-easonf-msft]

Hi , @mloc1990 

Not very clear.

Can you tell me more detail about what you want.

I create a calendar table  and I didn't fnd any problem in my test. 

Best Regards,
Community Support Team _ Eason

[mloc1990]

Hi @v-easonf-msft 

The original problem I'm trying to solve is to have 3 measures one for this week, one for the same period last week and one for the same period for 2 weeks ago.

The ones I originally posted were my attempts at creating these but they take full weeks in stead of similar time period.

thanks

[v-easonf-msft · Accepted solution]

Hi , @mloc1990 

Try measures as below:

This Week Measure =
IF (
    CALCULATE ( SUM ( 'Date'[Year Reference] ) ) = YEAR ( TODAY () )
        && CALCULATE ( SUM ( 'Date'[WeekNum] ) = WEEKNUM ( TODAY (), 2 ) ),
    1,
    BLANK ()
)

Previous Week Measure =
IF (
    CALCULATE ( SUM ( 'Date'[Year Reference] ) ) = YEAR ( TODAY () )
        && CALCULATE ( SUM ( 'Date'[WeekNum] ) )
            = WEEKNUM ( TODAY (), 2 ) - 1,
    1,
    BLANK ()
)

2 Weeks ago Measure =
IF (
    CALCULATE ( SUM ( 'Date'[Year Reference] ) ) = YEAR ( TODAY () )
        && CALCULATE ( SUM ( 'Date'[WeekNum] ) )
            = WEEKNUM ( TODAY (), 2 ) - 2,
    1,
    BLANK ()
)

Pbix attached

If  still not solved, can you show  me the  result you expect in excel.

Best Regards,
Community Support Team _ Eason

[amitchandak]

You can refer week rank for the same.

Please refer to file for example

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0