Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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()))
)
Solved! Go to 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |