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)

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)

```
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

- 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)

eDNA Solution from Heather Week-1.pbix (44.4 KB)

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

Hello @nikahafiz,

Thank you for referencing a Forum Post to add context to your question. The Post you replied to has been closed for a number of years, and we would ask that you close this Reply and create a new post to handle your question.

Thanks!

1 Like