Week on Week Comparison using Week Start

[yaman123]

Hi, 

I am looking to compare data on a week on week basis. I have a column in the date table which gives the start of each week on Monday - Sunday - 

Week Start Date(Mon-Sun) = 'Fiscal Calendar'[Date] - WEEKDAY('Fiscal Calendar'[Date],3)

I have measures in my tickets table which calculated tickets raised/ closed.  

Raised Tickets =

VAR maxSelectedDate = MAX('Fiscal Calendar'[Date])

VAR minSelectedDate = MIN('Fiscal Calendar'[Date])

RETURN

CALCULATE(COUNT('Table1'[Request Time Only]),'Table1'[Request Time Only] >= minSelectedDate && 'Table1'[Request Time Only] <= maxSelectedDate)

I am looking to do a weekly comparison of raised and closed tickets by category in a graph or so. I have a slicer which uses the Week Start date column so when i select a date, this should automatically calculate this week and last weeks data 

Thanks

[v-rzhou-msft · Accepted solution]

Hi @yaman123 ,

I create a sample to have a test. 

Data model:

Measure:

Raised Tickets = 
CALCULATE(COUNT(Table1[Request Time Only]))

Raised Tickets Last Week = 
VAR maxSelectedDate =
    MAX ( 'Fiscal Calendar'[Date] ) - 7
VAR minSelectedDate =
    MIN ( 'Fiscal Calendar'[Date] ) - 7
RETURN
    CALCULATE (
        COUNT ( 'Table1'[Request Time Only] ),
        FILTER (
            ALL ( Table1 ),
            'Table1'[Request Time Only] >= minSelectedDate
                && 'Table1'[Request Time Only] <= maxSelectedDate
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Thank you! This is working fine!

[amitchandak]

@yaman123 , Based on what I got. Closed joined with date table having columns like

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday week
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

example measures
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))

refer: https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e...