Sales Per Week Not Working in my model

[Hesham]

Hi everyone,

I feel like I am stuck in this simple concept, so I am wondering if anyone can cast their eyes and point me to where I am failing, thanks.

My data holds two simple tables:

1. Table 1 contains 2 columns, Date & Value.
2. Table 2 contains a Date table (with an active relationship with table 1 on date column)

I am trying to get the total sales value per week and per previous week.

A few attempts with no luck:

Reporting last week v2 = 
    CALCULATE( [Total Reporting],
        FILTER(ALL('Date'), 
            'Date'[Weeknum] = SELECTEDVALUE('Date'[Weeknum]) -1 && 
            'Date'[Fin Year] = SELECTEDVALUE('Date'[Fin Year])))


Reporting PW = 
VAR curwek= SELECTEDVALUE('Date'[Weeknum])
VAR curyr = SELECTEDVALUE('Date'[Fin Year])
VAR MaxWek = CALCULATE(MAX('Date'[Weeknum]),ALL('Date'))

RETURN

SUMX(
    FILTER(ALL('Date'),
        IF(curwek=1,
            'Date'[Weeknum] = MaxWek && 'Date'[Fin Year] = curyr -1,
            'Date'[Weeknum] = curwek -1 && 'Date'[Fin Year] = curyr)),
    [Total Reporting]
)

Here is a screenshot of the output:

Sample file (excel) [sample Sales per week.xlsx|attachment]
(upload://4aFf3uz8ERQ55mszfhHLXO9k4Dj.xlsx) (8.6 KB)
Power BI File sample Sales per week.pbix (187.3 KB)

Thanks in advance.
Hesham

[Melissa]

Hi @Hesham,

Thanks for provide a PBIX
I’ve replaced your Date table, created a relationship between Date and Business_Date (there was no active relationship in the file) and created these measures:

Total per week = 
VAR WeekEnding = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date], SELECTEDVALUE( 'Date'[WeekEnding] ))
VAR DDate = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date], SELECTEDVALUE( 'Date'[Date] ))
RETURN

IF(
    HASONEVALUE( 'Date'[WeekEnding] ) || HASONEVALUE( 'Date'[Date] ),
    CALCULATE( 
        [Total Reporting2],
        FILTER( ALL('Date'),
            'Date'[WeekOffset] = COALESCE( WeekEnding| DDate )
        )
    ),
    CALCULATE(
        [Total Reporting2],
        ALLSELECTED( 'Date' )
    )
)


Total this week = 
CALCULATE(
    [Total Reporting2],
    FILTER( 'Date',
        'Date'[WeekOffset] =0
    )
)


Total last week = 
CALCULATE(
    [Total Reporting2],
    FILTER( 'Date',
        'Date'[WeekOffset] =-1
    )
)


Total 2 weeks ago = 
CALCULATE(
    [Total Reporting2],
    FILTER( 'Date',
        'Date'[WeekOffset] =-2
    )
)


Total previous week = 
VAR WeekEnding = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date]| SELECTEDVALUE( 'Date'[WeekEnding] ))
VAR DDate = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date], SELECTEDVALUE( 'Date'[Date] ))
VAR ThisWeek = COALESCE( WeekEnding, DDate )
RETURN

IF(
    HASONEVALUE( 'Date'[WeekEnding] ) || HASONEVALUE( 'Date'[Date] ),
    CALCULATE( 
        [Total Reporting2],
        FILTER( ALL('Date'),
            'Date'[WeekOffset] = ThisWeek-1
        )
    ),
    CALCULATE(
        [Total Reporting2],
        FILTER( 
            ALLSELECTED( 'Date' ),
            'Date'[WeekOffset] <-1
        )
    )
)

.
Can you let me know if this is what you were after??

Here’s your file: sample Sales per week.pbix (208.1 KB)
I hope this was helpful.

[Hesham]

Hi @Melissa,

Thank you for your time and for highlighting the missing relationship in the date table. looking at your output, I am expecting the below table:

I am trying to debug the measures to see how the virtual tables look like … but not sure of the best approach.

The total return per week is based on the assumption that Monday is the start of the week and that all data points are taking on Friday’s. This will bring one expectation of 29/02/2020 (which is Saturday). But I found a solution in my original file to convert this back to Friday by using
IF(Data[Business_date]=DATE(2020,02,29), Data[Business_date],IF(WEEKDAY(Data[Business_date],2)=5,Data[Business_date],BLANK()))

Hopefully, the above will set the science.

Looking forward to hearing from you.

[Melissa]

Ok thanks for clearing that up @Hesham, this is what I have for you:

Measure for Current week:

Total Weekday <>1..4 = 
CALCULATE( [Total Reporting2],
    FILTER( 'Date',
        'Date'[DayOfWeek] IN {4, 5, 6} 
    )
)

Measure for Last week:

Total Weekday <>1..4 LW = 
VAR WeekEnding = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date]| SELECTEDVALUE( 'Date'[WeekEnding] ))
VAR DDate = LOOKUPVALUE( 'Date'[WeekOffset], 'Date'[Date], SELECTEDVALUE( 'Date'[Date] ))
VAR ThisWeek = COALESCE( WeekEnding, DDate )
RETURN

IF(
    HASONEVALUE( 'Date'[WeekEnding] ) || HASONEVALUE( 'Date'[Date] ),
    CALCULATE( 
        [Total Reporting2],
        FILTER( ALL('Date'),
            'Date'[WeekOffset] = ThisWeek-1 &&
            'Date'[DayOfWeek] IN {4, 5,  6}
        )
    ),
    CALCULATE(
        [Total Reporting2],
        FILTER( 
            ALLSELECTED( 'Date' ),
            'Date'[WeekOffset] <-1 &&
            'Date'[DayOfWeek] IN {4, 5,  6}
        )
    )
)

.
Then it looks like this:

Here is the updated file: sample Sales per week.pbix (209.5 KB)
I hope this is helpful.

[Hesham]

Thank you very much @Melissa. Working as expected.