Help with writing measure for previous week

[svishwanathan]

Good Afternoon

I have been asked to create several measures for previous week. For eg, I have a measure called total sum. The user wants to know a number for total sales side by side with total sales for previous week against dimensions like product,city

I have enhanced my calendar table to create relative week index. So I have rows that show 0 if date is in current week and -1 if date was in previous week and -2 if date was two weeks ago. The calendar date has dates from 2012 upto 2018

Now I am cannot proceed any further

When I try measures like, it returns no value

Productivity last week = CALCULATE([Productivity],FILTER('Calendar','Calendar'[Relative Week Index]="-1"))

Please help..I have been struggling with this problem for over a week now

[v-jiascu-msft]

Hi @svishwanathan,

Another try. Add a column of week number, then try a formula like this.

WeekNum = weeknum([date])

measure =
CALCULATE (
    [Productivity],
    FILTER (
        ALL ( 'Calendar' ),
        'Calendar'[WeekNum]
            = MIN ( 'Calendar'[WeekNum] ) - 1
    )
)

Best Regards!

Dale

[svishwanathan]

@ dale

Thanks for taking interest in this

Your solution works well but only when I provide a  date context. So if I have a table, then my dimension must be date or i have to specify the filter context somehwo

But say I want to create a table with Product as my dimension and want to have one column that has sales to date and another column that has sales last week, then this approach is yielding no results

Can yoy suggest any other work around

Swati

[v-jiascu-msft]

Hi @svishwanathan,

A date dimension is essential when you want a report about time. Let's discuss this with the example below.

Product		Date		     Amount
A		1/1/2010		10
A		1/2/2010		20
A		1/3/2010		30
A		1/4/2010		40
A		1/5/2010		50
A		1/6/2010		60
A		1/7/2010		70
A		1/8/2010		80
A		1/9/2010		90
A		1/10/2010		100
A		1/11/2010		110
A		1/12/2010		120
A		1/13/2010		130
A		1/14/2010		140
A		1/15/2010		150
A		1/16/2010		160
A		1/17/2010		170
A		1/18/2010		180
A		1/19/2010		190
A		1/20/2010		200

The report:

Item   AmountThisWeek   AmountLastWeek

A            ?                             ?

What should be the result of AmountThisWeek and AmountLastWeek? How can the formula know it? One way to do this is hardcoding the time in the formula, which is obvious a bad idea.

If you have some requirements for your scenario, please provide more information. Maybe we can find a workaround. If you can provide the PBIX file, that would be great.

Best Regards!

Dale

[Thyago_Rezende]

Hi @svishwanathan,

Try this:

CALCULATE([Productivity],
        FILTER('Calendar',

                   DATEADD ( 'Calendar'[Date], - 7,DAY)
         )
)

[svishwanathan]

Hello

Will this give me value for last 7 days or previous week

It did return a value so your formula works..I just cannot determine if the value returned is for last 7 days or previous week

[Thyago_Rezende]

This formula will propagate for the entire Calendar Table. If you put week column in pivot table, the formula should works.