WTD Spanning over New Year

[Anonymous]

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()))
)

[v-juanli-msft · Accepted solution]

Hi @Anonymous

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

[tarunsingla]

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.