Week and Previous Week

[cmcreynolds]

Okay, I am fighting fatigue and possibly the flu this morning and I have been stuck on this logic:

In a Power Pivot Data Model, I have a date field in my main table [AppDate]. I also have a calendar table with week number and a look up for that week number in my main table.

What I want is a pivot table that has one column for a week's total (count of the date or a dummy code?) and another column counting the previous week's total. I can't figure out whether to use a calculated column or a calculated field.

I know this is simple, but I just can't wrap my head around it today.

Please and thank you.

 

[ImkeF]

Hi Chris,
heads up - long weekend ahead!

You need to shift your dates 7 days back, as there is no WEEK-function in DAX. So if you take the week column into the row section of your pivot, there's no need for a special aggregation for the current week. Just take a simple SUM-measure.
For the previous week it will be:

PreviousWeek:=CALCULATE([SumMeasure]; DATEADD(DimDate[Date],-7,DAY))