Calculate Current and Previous Week

DAX DAX Calculations
1

Hello!
I thought this would be easy even though there are lots of topics on this but my solution is far from perfect.
I want to calculate Previous Week to date, Current Week to date and the Variance between both.
I am sure I got the previous week correctly, following Sam example on this platform.
My query is like so:

`> Previous Week Total =
VAR _NowWeek = SELECTEDVALUE ( Dates[Week Number] )
VAR _NowYear = SELECTEDVALUE ( Dates[Year] )
VAR _MaxWeekNumber = CALCULATE ( MAX ( Dates[Week Number] ), ALL ( Dates ))

RETURN
SUMX (
FILTER ( ALL ( Dates ),
IF ( _NowWeek = 1,
Dates[Week Number] = _MaxWeekNumber && Dates[Year] = _NowYear - 1,
Dates[Week Number] = _NowWeek - 1 && Dates[Year] = _NowYear )),
[Total Cases]
)

I am unable to reproduce the same for Current Week(Current Week, in this case, may not typically be this week but across selected time frames)
`
My file is attached
PreviousNcurrent.pbix (300.3 KB)

2

Hi @upwardD,

Give this a go an see how you get on with that. You are not leveraging the Extended Date table although I don’t see an obvious reason for that, you might want to look into that…

Currrent Week Total = 
VAR _cWeek = VALUES( Dates[WeekEnding] )
RETURN

CALCULATE( [Total Cases],
    FILTER( Dates,
        Dates[WeekEnding] IN _cWeek 
    )
)

.

Prev week total = 
CALCULATE( [Currrent Week Total],
    DATEADD( Dates[Date], -7, DAY )   
)

Gives me these results.

image
image573×578 15.8 KB

I hope this is helpful

2 Likes
3

Thank you so much.
Grateful

4

Ah, your Date Table makes this so simple!

Extended Date Table (Power Query M function) - M Code Showcase - Enterprise DNA Forum

1 Like