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)

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