DAX Calendar Formula for Dates Between Last 2 Week Ending Periods

[Anonymous]

Hello,

I have a DAX Calendar created like this:

Calendar =

ADDCOLUMNS (

CALENDAR ( DATE (YEAR(MIN('JCCD'[ActualDate])), 1, 1 ), DATE ( YEAR(NOW()), 12, 31 ) ),

"Week Ending", [Date] + ( ( 7 - WEEKDAY ( [Date] ) ) ),

"IsCurrentWeek", IF ( [Date] <= TODAY (), IF ( [Date] >= TODAY () - ( WEEKDAY ( TODAY () ) - 1 ), 1, 0 ), 0 ),

"IsLast14Days", IF ( [Date] <= TODAY (), IF ( [Date] >= TODAY () - 13, 1, 0 ), 0 ),

"IsLast30Days", IF ( [Date] <= TODAY (), IF ( [Date] >= TODAY () - 29, 1, 0 ), 0 ),

)

What I'm trying to add is a field that tells me if instead of IsLast14Days, if it's within the last 2 Week Ending periods.  So for example since today is 4/23, I want all the dates from 4/13 - 4/20 (last 2 previous week endings).  The ISLast14Days doesn't work for me as it doesn't calculate by Previous Week Ending.  I need to review the last 14, 30, 90 days by week ending dates and the formulas I have all calculate based on todays date.

I'm stumped on coming up with a forumula and looking for any additional brain power on this on.

Thanks!

[MFelix · Accepted solution]

Hi @Anonymous ,

To what I can understand you want to calculate your in two weeks based on the  last week day of past week so you need to add the weekday calculation to today try to use the following code:

Calendar =
VAR TodayWeekEnding =
    TODAY ()
        + ( ( 7 - WEEKDAY ( TODAY () ) ) )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ); 1; 1 ); TODAY () );
        "Week Ending"; [Date]
            + ( ( 7 - WEEKDAY ( [Date] ) ) );
        "IsCurrentWeek"; IF (
            [Date] <= TODAY ();
            IF ( [Date] >= TODAY () - ( WEEKDAY ( TODAY () ) - 1 ); 1; 0 );
            0
        );
        "IsLast14Days"; IF (
            [Date] <= TODAY ();
            IF ( [Date] <= TodayWeekEnding - 7 && [Date] >= TodayWeekEnding - 14; 1; 0 );
            0
        );
        "IsLast30Days"; IF ( [Date] <= TODAY (); IF ( [Date] >= TODAY () - 29; 1; 0 ); 0 )
    )

Just adjust the bold part for the 30 and 90 days.

Regards,

MFelix

[MFelix]

Hi @Anonymous ,

Try to use the following calculation_:

Calendar = 
VAR TodayWeekEnding =
    TODAY ()
        + ( ( 7 - WEEKDAY ( TODAY () ) ) )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ); 1; 1 ); TODAY () );
        "Week Ending"; [Date]
            + ( ( 7 - WEEKDAY ( [Date] ) ) );
        "IsCurrentWeek"; IF (
            [Date] <= TODAY ();
            IF ( [Date] >= TODAY () - ( WEEKDAY ( TODAY () ) - 1 ); 1; 0 );
            0
        );
        "IsLast14Days"; IF (
            [Date] <= TODAY ();
            IF ( [Date] <= TodayWeekEnding - 7 && [Date] >= TodayWeekEnding - 14; 1; 0 );
            0
        );
        "IsLast30Days"; IF ( [Date] <= TODAY (); IF ( [Date] >= TODAY () - 29; 1; 0 ); 0 )
    )

The bold part is what you need to copy and adjust for 30 days and 90 days.

Regards,

MFelix

[MFelix · Accepted solution]

Hi @Anonymous ,

To what I can understand you want to calculate your in two weeks based on the  last week day of past week so you need to add the weekday calculation to today try to use the following code:

Calendar =
VAR TodayWeekEnding =
    TODAY ()
        + ( ( 7 - WEEKDAY ( TODAY () ) ) )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ); 1; 1 ); TODAY () );
        "Week Ending"; [Date]
            + ( ( 7 - WEEKDAY ( [Date] ) ) );
        "IsCurrentWeek"; IF (
            [Date] <= TODAY ();
            IF ( [Date] >= TODAY () - ( WEEKDAY ( TODAY () ) - 1 ); 1; 0 );
            0
        );
        "IsLast14Days"; IF (
            [Date] <= TODAY ();
            IF ( [Date] <= TodayWeekEnding - 7 && [Date] >= TodayWeekEnding - 14; 1; 0 );
            0
        );
        "IsLast30Days"; IF ( [Date] <= TODAY (); IF ( [Date] >= TODAY () - 29; 1; 0 ); 0 )
    )

Just adjust the bold part for the 30 and 90 days.

Regards,

MFelix

[Anonymous · Accepted solution]

This was close enough for me to get it to the values I was after, THANK YOU!

The VAR was the key that I wasn't thinkng of.  Here is what I changed it to with an example:

Today is 4/24, the last 2 weeks ending values would be 4/7 - 4/20 (2 previous periods of weekening 4/20 and 4/13 which includes the dates of 4/7 - 4/13 and 4/14 - 4/20.  

Formula: 

Calendar =
VAR TodayWeekEnding =
    TODAY ()
        + ( ( 7 - WEEKDAY ( TODAY () ) ) )
RETURN
    ADDCOLUMNS (
        CALENDAR ( DATE ( YEAR ( TODAY () ); 1; 1 ); TODAY () );
        "Week Ending"; [Date]
            + ( ( 7 - WEEKDAY ( [Date] ) ) );
        "IsCurrentWeek"; IF (
            [Date] <= TODAY ();
            IF ( [Date] >= TODAY () - ( WEEKDAY ( TODAY () ) - 1 ); 1; 0 );
            0
        );
        "IsLast14Days"; IF (
            [Date] <= TODAY ();
            IF ( [Date] <= TodayWeekEnding - 7 && [Date] >= TodayWeekEnding - 20; 1; 0 );
            0
        );
        "IsLast30Days"; IF ( [Date] <= TODAY (); IF ( [Date] >= TODAY () - 29; 1; 0 ); 0 )
    )

[MFelix]

Hi @Anonymous ,

Glad I could help, please mark the correct answer in order to help other as well.

Regards,

MFelix

[Anonymous]

I am not seeing a option to mark as correct. I only am given the kudos options.  Where is the mark as correct option?

[MFelix]

You should have 3 buttons kudos, reply and accept as solution.

Regards,
MFelix

[Anonymous]

For some reason I do not.

[MFelix]

I noticed that you have two nicks one prometheus and prometheus_2 the original post was made in the first nick.

Only the original user o posted, super users and admins can accept answers on a post
.
Why do you have two nicks?

I have accepted the solution for you.

Regards,
MFelix