cancel
Showing results for
Did you mean:
Regular Visitor

## WTD Spanning over New Year

I have the below function but with the switch to the new year it's not picking up monday (12/31/2018). How can I get it to pull the full current week?

Locked This Week to Date Units = CALCULATE(
COUNT(LoanMaster[Loan Number]),
ALL('Locked Date'),
FILTER(
'Locked Date',
'Locked Date'[WeekOfYear] = WEEKNUM(today())
&& 'Locked Date'[Year] = YEAR(today()))
)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: WTD Spanning over New Year

Assume the original "WeekofYear" in your "Date" table starts from Monday and ends at Sunday.

I create calcuated column named "week" which functions as your "WeekofYear"

```year = YEAR('calendar'[Date])

week = WEEKNUM('calendar'[Date],2)```

in this specific scenario, you need to create other columns in that date table

```year2 = IF([week]>52,[year]+1,[year])

week2 = IF([week]>52,1,[week])```

Then create a measure in your "LoanMaster" table

```Measure =
VAR date1 =TODAY()
VAR spefic =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER ( 'calendar', [year] = YEAR ( date1 ) && [week] = WEEKNUM ( date1 ) )
)
VAR cust =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER ( 'calendar', [year2] = YEAR ( date1 ) && [week2] = WEEKNUM ( date1 ) )
)
RETURN
IF ( WEEKNUM ( date1, 2 ) > 52, spefic, cust )```

i make several tests as below

date1 =date(2018,12,27)  assume today=2018/12/27

date1 =date(2018,12,31)  assume today=2018/12/31

date1 =date(2019,1,4)  assume today=2019/1/4

Best Regards

Maggie

2 REPLIES 2
Established Member

## Re: WTD Spanning over New Year

Is 'Locked Date'[WeekOfYear] a calculated column ? What value does it have for Dec 31, 2018. If it is a calculated column, you might want to adjust the formula to consider the days in 53rd week of 2018, as 1st week of 2019.

Like this:

WeekOfYear = IF(WEEKNUM(Sheet1[YourSampleDate]) > 52, 1, WEEKNUM(Sheet1[YourSampleDate]))

Year = IF(WEEKNUM(Sheet1[YourSampleDate]) = 53, YEAR([YourSampleDate]) +1, YEAR(Sheet1[YourSampleDate]))

Similar tweak required in your "Locked This Week to Date Units", only if WeekOfYear can not be updated.

Community Support Team

## Re: WTD Spanning over New Year

Assume the original "WeekofYear" in your "Date" table starts from Monday and ends at Sunday.

I create calcuated column named "week" which functions as your "WeekofYear"

```year = YEAR('calendar'[Date])

week = WEEKNUM('calendar'[Date],2)```

in this specific scenario, you need to create other columns in that date table

```year2 = IF([week]>52,[year]+1,[year])

week2 = IF([week]>52,1,[week])```

Then create a measure in your "LoanMaster" table

```Measure =
VAR date1 =TODAY()
VAR spefic =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER ( 'calendar', [year] = YEAR ( date1 ) && [week] = WEEKNUM ( date1 ) )
)
VAR cust =
CALCULATE (
SUM ( Sheet3[sales] ),
FILTER ( 'calendar', [year2] = YEAR ( date1 ) && [week2] = WEEKNUM ( date1 ) )
)
RETURN
IF ( WEEKNUM ( date1, 2 ) > 52, spefic, cust )```

i make several tests as below

date1 =date(2018,12,27)  assume today=2018/12/27

date1 =date(2018,12,31)  assume today=2018/12/31

date1 =date(2019,1,4)  assume today=2019/1/4

Best Regards

Maggie

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!