Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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
v-juanli-msft
Community Support
Community Support

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

6.png

 

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

7.png

 

 

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

8.png

 

Best Regards

Maggie

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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

6.png

 

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

7.png

 

 

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

8.png

 

Best Regards

Maggie

tarunsingla
Solution Sage
Solution Sage

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.