Current Week Calculation


#1

Another Day Another Query,

Sorry for all the Questions but just gone into a new job and using a lot of time intelligence that I haven’t had to use in the past.

I have a Measure Total Quantity and I want to Calculate the Current Week and each of the 5 Previous weeks. What does the Current Week measure look like?

Once I have the Current Week measure do I just have 5 other measures that use the DateAdd Function -1, -2 etc or is there another clever way?

Thanks

Chris


#2

Hi Chris, check out this forum post from a week back.

This shows you all the custom calendar and weekly work I’ve put out there around time intelligence.

You will just need a small derivative of these. Most like the week on week change I think.

See how you go with these.

If not getting you there post back


#3

Hi Sam,

Thanks for the reply.

How would the calculation look if I was to use the latest date in the fact table rather than a selected value.

Cheers

Chris


#4

So I think your actually look for a rolling total really. Would that be right?

You want to on any day look back over 5 weeks and see how what the total aggregation is?

Something simple like this would do it then I would have thought

Previous 5 Week Sales = 
CALCULATE( [Total Sales],
    DATESBETWEEN( Dates[Date], MAX( Dates[Date] ) - 35, MAX( Dates[Date] ) ) )

Can you add some images of what you mean if I’m not understanding this correctly.

I’m having to guess what the context/model is etc. which makes it difficult

Thanks


#5

Hi Sam

I want a grid with the 5 previous weekly totals backwards from the Current Day. So when I get to next week the first total will be from this week.

Does that make sense?

Chris


#6

Look into think really your best bet is 5 measures to get those results.

Actually you could also have a matrix where the columns will be divided up by 1,2,3,4,5. (You would need to create a need table with those number in it.

Then use SELECTVALUE(WeekColumn) within you measure to dynamically create the results for the right week.

This make sense?

Can be done, now that I know what you need