Last Week and Week to Date Sales

[sabeensp]

Hello All,

I have a simple model Date Dim and Sales Fact table both connected via Date Column (1:M).

I need to find:

1 - Need Sum of Revenue of Only Last Week

2- Need Week to Date Revenue (Week to Date is consider between Last Sunday and Previous completed day. So if today is Friday then Week To Date is sum of Revenue Between Last Sun and Yesturday (Thursday)

You can download the PBIX from here.
https://1drv.ms/u/s!AumyvtVXEve9jMIcjTnpRN4Op0yqrA

Thanks in advance

[v-frfei-msft · Accepted solution]

Hi @sabeensp ,

To create the measures as below.

Last Week = 
VAR previousweek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () ) - 1
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER ( DimDate, DimDate[Year] * 100 + DimDate[WeekOfYear] = previousweek )
    )

Week to Date Revenue = 
VAR thisweek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () )
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER (
            DimDate,
            DimDate[Year] * 100 + DimDate[WeekOfYear] = thisweek
                && DimDate[FullDateUSA] < TODAY ()
        )
    )

For more details, please check the pbix as attached.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @sabeensp ,

To create the measures as below.

Last Week = 
VAR previousweek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () ) - 1
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER ( DimDate, DimDate[Year] * 100 + DimDate[WeekOfYear] = previousweek )
    )

Week to Date Revenue = 
VAR thisweek =
    YEAR ( TODAY () ) * 100
        + WEEKNUM ( TODAY () )
RETURN
    CALCULATE (
        SUM ( Sales[Revenue] ),
        FILTER (
            DimDate,
            DimDate[Year] * 100 + DimDate[WeekOfYear] = thisweek
                && DimDate[FullDateUSA] < TODAY ()
        )
    )

For more details, please check the pbix as attached.

Regards,
Frank

[v-frfei-msft · Accepted solution]

Hi @sabeensp ,

Does that make sense? If so, kindly mark my answer as the solution to close the case please. Thanks in advance.

Regards,
Frank