Current Week Calculation

[chris.turnbull]

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

[sam.mckay]

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

Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard)

Hi there, Yes as you’ve seen unfortunately time intelligence calculations don’t work for fiscal weeks. Here’s are some videos that talk through how to solve this. This is the ultimate formula you would need Previous Year Sales = VAR CurrentMonth = SELECTEDVALUE( Dates[Week Number] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) RETURN CALCULATE( [Total Sales], FILTER( ALL( Dates ), Dates[Week Number] = CurrentMonth && Dates[Year] = CurrentYear - 1))

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

[chris.turnbull]

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

[sam.mckay]

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

[chris.turnbull]

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

[sam.mckay]

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