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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dwel0120
Helper III
Helper III

Current Rate of Current Week

I am trying to get the current rate from a column but also needs to be pulling from the most current week of the year. Any thoughts or ideas? The year's current week changes every week, so I need to pull the most current week for when I refresh data. I already made the CurrentWk metric and the CRate metric but nee CurrentRate

CurrentWk = CALCULATE(Max('TABLE1'[WkNm]),'TABLE1'[SchoolYear] = "SY20/21")
CRate = DIVIDE(sumx('TABLE1','TABLE1[CumWDs]),'TABLE1'[CumDenominator],"")
 

This formula isn't working.

CurrentRate = CALCULATE('TABLE1'[CRate], FILTER(ALL('TABLE1'[WkNm]), 'TABLE1'[WkNm] = 'TABLE1'[CurrentWk]))
6 REPLIES 6
amitchandak
Super User
Super User

@dwel0120 , Try like

CurrentRate = CALCULATE('TABLE1'[CRate], FILTER(ALL('TABLE1'), 'TABLE1'[WkNm] = 'TABLE1'[CurrentWk]))

 

or better a separate table for week and try like

CurrentRate = CALCULATE('TABLE1'[CRate], FILTER(ALL('Week'), 'Week'[WkNm] = 'TABLE1'[CurrentWk]))

 

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak Neither solution worked for me. I tried CurrentRate = CALCULATE('TABLE1'[CRate], FILTER(ALL('TABLE1'), 'TABLE1'[WkNm] = 'TABLE1'[CurrentWk])) and it didn't come up with the correct number and then I created a new "Date" table and tried CurrentCWMRate = CALCULATE ('CwmData'[CwmRate],  FILTER(ALL('Date'), 'Date'[Week Number] = 'CwmData'[CurrentWk])) and that didn't work either. When I tried the new Date table it came up BLANK.

@dwel0120 ,Can you share sample data and sample output in table format?

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

@amitchandak 

SchoolYearWeekOfWkNmSchoolIDSchoolGroupRegionGradeBandRetentionModelStudentCohortSeasonStartCumAddsCumWDsCumInElWDsCumTransferInCumTransferOutRecordType
SY18/19Wednesday, November 21, 2018251220CWRHSNew000000D
SY18/19Wednesday, March 20, 2019421220CWRHSNew000000D
SY18/19Wednesday, July 25, 201881220CWRHSReturning000000D
SY19/20Wednesday, October 2, 2019185840CWRMSReturning000000D
SY19/20Wednesday, October 23, 2019215840CWRMSReturning000000D
SY19/20Wednesday, February 19, 2020385840CWRMSReturning000000D
SY19/20Wednesday, September 4, 2019145840CWRMSReturning000000D
SY20/21Wednesday, August 19, 2020121007CWRK5Returning000000D
SY20/21Wednesday, August 19, 2020121080CWRK5New000000D
SY20/21Wednesday, August 19, 2020121080CWRK5Returning000000D

 

DateMonthYearWeek Start dateWeek End dateWeek NumberWeekWeekdayWeekDay NameMonthSort
1/1/2005 0:00January200512/27/2004 0:001/2/2005 0:0012005016Sat2005-01
1/2/2005 0:00January200512/27/2004 0:001/2/2005 0:0012005017Sun2005-01
3/10/2019 0:00March20193/4/2019 0:003/10/2019 0:00102019107Sun2019-03
3/11/2019 0:00March20193/11/2019 0:003/17/2019 0:00112019111Mon2019-03
3/12/2019 0:00March20193/11/2019 0:003/17/2019 0:00112019112Tue2019-03
5/4/2020 0:00May20205/4/2020 0:005/10/2020 0:00192020191Mon2020-05
5/5/2020 0:00May20205/4/2020 0:005/10/2020 0:00192020192Tue2020-05
5/6/2020 0:00May20205/4/2020 0:005/10/2020 0:00192020193Wed2020-05
5/7/2020 0:00May20205/4/2020 0:005/10/2020 0:00192020194Thu2020-05
5/8/2020 0:00May20205/4/2020 0:005/10/2020 0:00192020195Fri2020-05

 

@amitchandak datasets are above

Anonymous
Not applicable

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.