Dax for previous date calculations

Anonymous For this type of time intelligence a date table will make your life a bit easier 🙂  I have the last week, last month, and last year of the weekly amounts created for you in the attached PBIX. Once you have a date table, the measures can be created.

Date table I used, (Modeling --> New Table):

Date =
ADDCOLUMNS (
CALENDAR (
DATE ( YEAR ( MIN ( 'Table'[WEEK_ENDING] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Table'[WEEK_ENDING] ) ), 12, 31 )
),
"Month", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ),
"Year", DATE ( YEAR ( [Date] ), 1, 1 ),
"WeekOf",
[Date] - WEEKDAY ( [Date], 1 ) + 1,
"Monthly Week Number",
WEEKNUM ( [Date], 1 )
- WEEKNUM ( DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 ), 1 ) + 1,
"Yearly Week Number", WEEKNUM ( [Date] )
)

and the measures:

Hi Anonymous ,

If you don't want to create a calendar table, you can try this:

Week Number = WEEKNUM(MAX('Table'[WEEK_ENDING]),2)

Month WeekNum = 
VAR CurrentWeekEnding = MAX ( 'Table'[WEEK_ENDING] )
RETURN
WEEKNUM ( CurrentWeekEnding, 2 )
    - WEEKNUM (
        DATE ( YEAR ( CurrentWeekEnding ), MONTH ( CurrentWeekEnding ), 1 ),
        2
    ) + 1

Last Week STARTS = 
SUMX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[WEEK_ENDING]
            = MAX ( 'Table'[WEEK_ENDING] ) - 7
    ),
    [STARTS]
)

Last Month STARTS = 
VAR CurrentMonthWeekNum = [Month WeekNum]
VAR CurrentWeekEnding =
    MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
    YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
    MONTH ( CurrentWeekEnding )
RETURN
    SWITCH (
        CurrentMonth,
        1,
            SUMX (
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear - 1
                        && MONTH ( 'Table'[WEEK_ENDING] ) = 12
                        && [Month WeekNum] = CurrentMonthWeekNum
                ),
                [STARTS]
            ),
        SUMX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear
                    && MONTH ( 'Table'[WEEK_ENDING] ) = CurrentMonth - 1
                    && [Month WeekNum] = CurrentMonthWeekNum
            ),
            [STARTS]
        )
    )

Last Year STARTS = 
VAR CurrentYearWeekNum = [Week Number]
VAR CurrentWeekEnding =
    MAX ( 'Table'[WEEK_ENDING] )
VAR CurrentYear =
    YEAR ( CurrentWeekEnding )
VAR CurrentMonth =
    MONTH ( CurrentWeekEnding )
RETURN
SUMX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                YEAR ( 'Table'[WEEK_ENDING] ) = CurrentYear-1
                    && [Week Number] = CurrentYearWeekNum
            ),
            [STARTS]
        )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.