# New Enterprise DNA Initiatives # Measure previous week

I cannot cope with creating a measure. The measure should show units for the previous week.
Note: I cannot use calculated tables and columns because I have a live connection.

Week-1.pbix (43.7 KB)

@Harris

``````Previous Week =
VAR CurrentWeek =
MAX ( DimDate[WeekInYear] )
VAR PreviousWeek =
CALCULATE (
MAX ( DimDate[WeekInYear] ),
DimDate[WeekInYear] < CurrentWeek,
REMOVEFILTERS ( DimDate )
)
VAR Result =
CALCULATE (
SUM ( Sales[Units] ),
TREATAS ( { PreviousWeek }, DimDate[WeekInYear] )
)
RETURN
Result
``````

or the below one considering your expertise with DAX.

``````Previous Week =
VAR CurrentWeek =
MAX ( DimDate[WeekInYear] )
VAR PreviousWeek =
CALCULATE (
MAX ( DimDate[WeekInYear] ),
DimDate[WeekInYear] < CurrentWeek,
REMOVEFILTERS ( DimDate )
)
VAR Result =
CALCULATE ( SUM ( Sales[Units] ), DimDate[WeekInYear] = PreviousWeek )
RETURN
Result``````

I see there is already a response from @AntrikshSharma

but I wanted to contribute my thoughts and notes as well

1. The date table provided in your sample is very thin, I recommend including a few more basic columns if possible, such as report that combines week and year as numbers
Example for G2019, week 1 - I would have a column that shows 201901
I would probably also include the year as a number, without the “G” included (this makes math a bit easier)

The first measure I came up with was similar to the second solution above:

Units in Prior Week =
VAR SelectedWeek = SELECTEDVALUE( DimDate[WeekInYear] )
VAR SelectedYear = SELECTEDVALUE( DimDate[CalendarYearName] )
RETURN
CALCULATE( [Actual Units in Week],
FILTER( ALL( DimDate ),
DimDate[WeekInYear] = SelectedWeek -1 &&
DimDate[CalendarYearName] = SelectedYear))

But I noticed that this returned a blank on Week 1, so I modified it to this:

Units in Prior Week (dealing with year change) =
VAR SelectedWeek = SELECTEDVALUE( DimDate[WeekInYear] )
VAR SelectedYear = SELECTEDVALUE( DimDate[CalendarYearName] )
VAR YearAsNum = VALUE( RIGHT( SelectedYear, 4 ))

VAR NormalWeek =
CALCULATE( [Actual Units in Week],
FILTER( ALL( DimDate ),
DimDate[WeekInYear] = SelectedWeek -1 &&
DimDate[CalendarYearName] = SelectedYear))

VAR HandleWeek1 =
CALCULATE( [Actual Units in Week],
FILTER( ALL( DimDate ),
DimDate[WeekInYear] = 53 &&
DimDate[CalendarYearName] = CONCATENATE(“G”, YearAsNum - 1)))

RETURN
IF( SelectedWeek = 1,
HandleWeek1,
NormalWeek )

Notice that because “Year” is a text value, with a G included, I had to do some fancy footwork to return the prior year for the "HandleWeek1 part of the measure)

3 Likes

@Heather Thanks for the idea. I added Year Week Number column and have updated my code. Harris.pbix (63.6 KB)

``````Previous Week =
VAR CurrentWeek =
MAX ( DimDate[Year Week Number] )
VAR PreviousWeek =
CALCULATE (
MAX ( DimDate[Year Week Number] ),
DimDate[Year Week Number] < CurrentWeek,
REMOVEFILTERS ( DimDate )
)
VAR Result =
CALCULATE (
[Actual Units in Week],
TREATAS ( { PreviousWeek }, DimDate[Year Week Number] ),
REMOVEFILTERS ( DimDate )
)
RETURN
Result
``````

.

``````Previous Week 2 =
VAR CurrentWeek =
MAX ( DimDate[Year Week Number] )
VAR PreviousWeek =
CALCULATE (
MAX ( DimDate[Year Week Number] ),
DimDate[Year Week Number] < CurrentWeek,
REMOVEFILTERS ( DimDate )
)
VAR Result =
CALCULATE (
[Actual Units in Week],
DimDate[Year Week Number] = PreviousWeek,
REMOVEFILTERS ( DimDate )
)
RETURN
Result``````
4 Likes