Measure previous week

DAX DAX Calculations
1

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.

Image
Image1186×710 30.6 KB

Week-1.pbix (43.7 KB)

2

@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
3

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)

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

3 Likes
4

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

image
image967×320 10.1 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
6

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