power BI 如何計算WTD

如何計算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")  //回傳最小一天.

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

)