Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dax for previous date calculations

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.

 

Need Help
  • DataZoe's avatar
    DataZoe
    5 years ago

    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's avatar
    Icey
    5 years ago

    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.

2 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    5 years ago

    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.