topic Re: Current Rate of Current Week in DAX Commands and Tips

Current Rate of Current Week

dwel0120 — Thu, 29 Oct 2020 14:02:20 GMT

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

Re: Current Rate of Current Week

Anonymous — Thu, 29 Oct 2020 14:30:59 GMT

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Re: Current Rate of Current Week

amitchandak — Thu, 29 Oct 2020 16:30:07 GMT

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-Last-Week/ba-p/1051123

Re: Current Rate of Current Week

dwel0120 — Thu, 29 Oct 2020 18:09:28 GMT

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.

Re: Current Rate of Current Week

amitchandak — Fri, 30 Oct 2020 10:39:17 GMT

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

Re: Current Rate of Current Week

dwel0120 — Fri, 30 Oct 2020 15:51:16 GMT

amitchandak

SchoolYear	WeekOf	WkNm	SchoolID	SchoolGroup	Region	GradeBandRetentionModel	StudentCohort	SeasonStart	CumAdds	CumWDs	CumInElWDs	CumTransferIn	CumTransferOut	RecordType
SY18/19	Wednesday, November 21, 2018	25	1220	C	WR	HS	New	0	0	0	0	0	0	D
SY18/19	Wednesday, March 20, 2019	42	1220	C	WR	HS	New	0	0	0	0	0	0	D
SY18/19	Wednesday, July 25, 2018	8	1220	C	WR	HS	Returning	0	0	0	0	0	0	D
SY19/20	Wednesday, October 2, 2019	18	5840	C	WR	MS	Returning	0	0	0	0	0	0	D
SY19/20	Wednesday, October 23, 2019	21	5840	C	WR	MS	Returning	0	0	0	0	0	0	D
SY19/20	Wednesday, February 19, 2020	38	5840	C	WR	MS	Returning	0	0	0	0	0	0	D
SY19/20	Wednesday, September 4, 2019	14	5840	C	WR	MS	Returning	0	0	0	0	0	0	D
SY20/21	Wednesday, August 19, 2020	12	1007	C	WR	K5	Returning	0	0	0	0	0	0	D
SY20/21	Wednesday, August 19, 2020	12	1080	C	WR	K5	New	0	0	0	0	0	0	D
SY20/21	Wednesday, August 19, 2020	12	1080	C	WR	K5	Returning	0	0	0	0	0	0	D

Date	Month	Year	Week Start date	Week End date	Week Number	Week	Weekday	WeekDay Name	MonthSort
1/1/2005 0:00	January	2005	12/27/2004 0:00	1/2/2005 0:00	1	200501	6	Sat	2005-01
1/2/2005 0:00	January	2005	12/27/2004 0:00	1/2/2005 0:00	1	200501	7	Sun	2005-01
3/10/2019 0:00	March	2019	3/4/2019 0:00	3/10/2019 0:00	10	201910	7	Sun	2019-03
3/11/2019 0:00	March	2019	3/11/2019 0:00	3/17/2019 0:00	11	201911	1	Mon	2019-03
3/12/2019 0:00	March	2019	3/11/2019 0:00	3/17/2019 0:00	11	201911	2	Tue	2019-03
5/4/2020 0:00	May	2020	5/4/2020 0:00	5/10/2020 0:00	19	202019	1	Mon	2020-05
5/5/2020 0:00	May	2020	5/4/2020 0:00	5/10/2020 0:00	19	202019	2	Tue	2020-05
5/6/2020 0:00	May	2020	5/4/2020 0:00	5/10/2020 0:00	19	202019	3	Wed	2020-05
5/7/2020 0:00	May	2020	5/4/2020 0:00	5/10/2020 0:00	19	202019	4	Thu	2020-05
5/8/2020 0:00	May	2020	5/4/2020 0:00	5/10/2020 0:00	19	202019	5	Fri	2020-05

Re: Current Rate of Current Week

dwel0120 — Mon, 02 Nov 2020 18:40:48 GMT

amitchandak datasets are above