Dax for previous date calculations

[Anonymous]

Hi,

I have a week ending date column and starts revenue in my report.

I have to build a report which shows 

How many starts last week compared to the previous week, previous month, previous year , like shown below

How many starts last month compared to the previous month, previous year

How many starts last year compared to the previous year

How many starts last week compared to the cumulative starts for the same period of the previous year

Can you please help me how to create the DAX for the above requirement as I have weekending column as date column.

Thanks & Regards,

Neelofar Shama.

[DataZoe · Accepted solution]

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

Total Starts = sum('Table'[STARTS])

Last Week =
VAR lw =
SELECTEDVALUE ( 'Date'[Date] ) - 7
RETURN
IF ( ISBLANK ( lw ), BLANK (), CALCULATE ( [Total Starts], 'Date'[Date] = lw ) )

Last Month =
VAR wn =
SELECTEDVALUE ( 'Date'[Monthly Week Number] )
RETURN
IF (
OR ( ISBLANK ( wn ), ISBLANK ( [Total Starts] ) ),
BLANK (),
CALCULATE (
[Total Starts],
PREVIOUSMONTH ( 'Date'[Date] ),
'Date'[Monthly Week Number] = wn
)
)

Last Year =
VAR wn =
SELECTEDVALUE ( 'Date'[Yearly Week Number] )
RETURN
IF (
OR ( ISBLANK ( wn ), ISBLANK ( [Total Starts] ) ),
BLANK (),
CALCULATE (
[Total Starts],
PREVIOUSYEAR ( 'Date'[Date] ),
'Date'[Yearly Week Number] = wn
)
)

Hope this helps!

[Icey · Accepted solution]

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.

[DataZoe · Accepted solution]

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

Total Starts = sum('Table'[STARTS])

Last Week =
VAR lw =
SELECTEDVALUE ( 'Date'[Date] ) - 7
RETURN
IF ( ISBLANK ( lw ), BLANK (), CALCULATE ( [Total Starts], 'Date'[Date] = lw ) )

Last Month =
VAR wn =
SELECTEDVALUE ( 'Date'[Monthly Week Number] )
RETURN
IF (
OR ( ISBLANK ( wn ), ISBLANK ( [Total Starts] ) ),
BLANK (),
CALCULATE (
[Total Starts],
PREVIOUSMONTH ( 'Date'[Date] ),
'Date'[Monthly Week Number] = wn
)
)

Last Year =
VAR wn =
SELECTEDVALUE ( 'Date'[Yearly Week Number] )
RETURN
IF (
OR ( ISBLANK ( wn ), ISBLANK ( [Total Starts] ) ),
BLANK (),
CALCULATE (
[Total Starts],
PREVIOUSYEAR ( 'Date'[Date] ),
'Date'[Yearly Week Number] = wn
)
)

Hope this helps!