cancel
Showing results for
Did you mean:
Highlighted
KGooseman 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
tarunsingla 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 #### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform. #### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work Top Kudoed Authors
Users Online
Currently online: 38 members 1,063 guests
Recent signins:
• jhern90 • venkateshsriram • laurelning • azimme15 • Anthony_W • bojan_jankic_84 • perezmarcone • jphilippeh • swang123 • Papadopoulos • timmur • sanjib • aalfonsoi • BrianSchaffTMC 