DAX formula to show a value for the same day of the week over the past six weeks (by week)

[mikemi]

I stitched together a measure "Trials Last Week" that shows values for last Sunday (coming from a Salesforce snapshot report that has dozens of rows for every day over the past few months). I created two boolean calculated columns "isLastWeek" (at the bottom of this post) and "Day" to use as filters in the measure.

How do I create versions of "isLastWeek" for two Sundays ago, three Sundays ago...eight Sundays ago so I can match them to measures for the same number of weeks ago? I know creating several calculated columns and measures isn't the cleanest approach, but it's fine for now.

Trials Last Week = 
CALCULATE (
    COUNTA ( 'Daily Snapshot'[Stage] ),
    'Daily Snapshot'[Stage] = "4. Trial",
    'Daily Snapshot'[isLastWeek] = TRUE (),
    'Daily Snapshot'[Day] = "Sunday",
    'Daily Snapshot'[Status] <> "Inactive"
)

Day =
FORMAT ( 'Daily Snapshot'[Date], "DDDD" )

isLastWeek = 
IF (
    WEEKNUM ( TODAY (), 1 ) = 1,
    IF (
        AND (
            YEAR ( 'Daily Snapshot'[Date] )
                = YEAR ( TODAY () ) - 1,
            WEEKNUM ( 'Daily Snapshot'[Date] )
                = WEEKNUM ( DATE ( YEAR ( TODAY () ), MONTH ( 12 ), DAY ( 31 ) ) )
        ),
        TRUE (),
        FALSE ()
    ),
    IF (
        AND (
            YEAR ( 'Daily Snapshot'[Date] ) = YEAR ( TODAY () ),
            WEEKNUM ( 'Daily Snapshot'[Date], 1 ) = WEEKNUM ( TODAY (), 1 )
        ),
        TRUE (),
        FALSE ()
    )
) 

[v-lili6-msft · Accepted solution]

hi,@mikemi

   You can use your measure isLastWeek do these with a little modification as below:

isLastWeek2 = 
IF (
    WEEKNUM ( TODAY (), 1 ) = 1,
    IF (
        AND (
            YEAR ( 'Daily Snapshot'[Date] )
                = YEAR ( TODAY () ) - 1,
            WEEKNUM ( 'Daily Snapshot'[Date] )
                = WEEKNUM ( DATE ( YEAR ( TODAY () ), MONTH ( 12 ), DAY ( 31 ) ) )
        ),
        TRUE (),
        FALSE ()
    ),
    IF (
        AND (
            YEAR ( 'Daily Snapshot'[Date] ) = YEAR ( TODAY () ),
            WEEKNUM ( 'Daily Snapshot'[Date], 1 )+1 = WEEKNUM ( TODAY (), 1 )
        ),
        TRUE (),
        FALSE ()
    )
) 

Result:

 

Be careful where the red box line is marked:

Best Regards,

Lin

[mikemi]

Thank you! This worked.