如何計算WTD前提, 最重要是找到對的週下面是在找對的週期數的語法,需注意一下
WEEKNUM(<date>, <return_type>)
Return type: 1, 星期的開始是星期日. 為數1開始 . 此為預設值.
Return type: 2, 星期的開始是星期一. 為數1開始 .
最近一週期 =
VAR TodaysDate = TODAY()
VAR TodaysWeekNum = WEEKNUM(TodaysDate,2)
RETURN TodaysWeekNum
First Working Day of Week =
VAR TodaysDate = TODAY()
// 建立一變數是1/1日開始到12/31日
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekNum1",WEEKNUM([Date],2)) //週期數
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2)) //星期數
VAR TodaysWeekNum = WEEKNUM(TodaysDate,2) //目前日期的週期數
VAR Calendar4 = FILTER(Calendar3,[WeekNum1]=TodaysWeekNum&&[WeekDay1]<6)
VAR FirstWorkingDay = MINX(Calendar4,[Date]) // 找出這週的最小一天
RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy") //回傳最小一天.
VAR TodaysDate = TODAY()
VAR TodaysWeekNum = WEEKNUM(TodaysDate,2)
RETURN TodaysWeekNum
First Working Day of Week =
VAR TodaysDate = TODAY()
// 建立一變數是1/1日開始到12/31日
VAR Calendar1 = CALENDAR(DATE(YEAR(TodaysDate),1,1),DATE(YEAR(TodaysDate),12,31))
VAR Calendar2 = ADDCOLUMNS(Calendar1,"WeekNum1",WEEKNUM([Date],2)) //週期數
VAR Calendar3 = ADDCOLUMNS(Calendar2,"WeekDay1",WEEKDAY([Date],2)) //星期數
VAR TodaysWeekNum = WEEKNUM(TodaysDate,2) //目前日期的週期數
VAR Calendar4 = FILTER(Calendar3,[WeekNum1]=TodaysWeekNum&&[WeekDay1]<6)
VAR FirstWorkingDay = MINX(Calendar4,[Date]) // 找出這週的最小一天
RETURN FORMAT(FirstWorkingDay,"mm/dd/yyyy") //回傳最小一天.
WTD 計算可以參考如下
A:
WTD Current = CALCULATE( SUM('Table'[Value])
,Filter('Table',[WeekNum]=WEEKNUM(TodaysDate,2) )
)
B;
WTD Current =
IF (
HASONEVALUE ( Dates[ISO Year] )
&& HASONEVALUE (Dates[ISO Week Number] ),
CALCULATE (
SUM ( Sales[Sales Amount] ),
FILTER (
ALL ( Dates ),
Dates[ISO Year] = VALUES ( Dates[ISO Year] )
&& Dates[ISO Week Number] = VALUES ( Dates[ISO Week Number] )
&& Dates[Date] <= MAX ( Dates[Date] )
)
),
BLANK ()
)
