WTD slicer not showing correct results

[Junaid11]

Hello,

I have created WTD/MTD/QTD/YTD  slicer based on below formula. The formulas are working fine when in Data Pane on Left when I choose WTD it shows currrent week  which is completely correct as seen in below picture.

WTD/MTD/QTD/YTD =

VAR TodayDate = TODAY()

VAR YearStart = CALCULATE(STARTOFYEAR(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate) )

VAR QuarterStart = CALCULATE(STARTOFQUARTER(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate),QUARTER(PuigWeeklySkuByStore_data2[Month Year]) = QUARTER(TodayDate))

VAR MonthStart = CALCULATE(STARTOFMONTH(PuigWeeklySkuByStore_data2[Month Year]),YEAR(PuigWeeklySkuByStore_data2[Month Year]) = YEAR(TodayDate),MONTH(PuigWeeklySkuByStore_data2[Month Year]) = MONTH(TodayDate))

VAR WeekStart = CALCULATE(TodayDate-WEEKDAY(TodayDate,2),YEAR(PuigWeeklySkuByStore_data2[Month Year])=YEAR(TodayDate))

VAR Result =

UNION(

ADDCOLUMNS( CALENDAR(YearStart,TodayDate), "Selection", "YTD"),

ADDCOLUMNS( CALENDAR(QuarterStart,TodayDate), "Selection","QTD"),

ADDCOLUMNS( CALENDAR(MonthStart,TodayDate), "Selection","MTD"),

ADDCOLUMNS( CALENDAR(WeekStart,TodayDate), "Selection","WTD")

)

RETURN Result

But when I am using this slicer of WTD to show data in table so it is also showing previous week data. It is showing previous and weeks ahead date along with current week as shown in picture:

Do I need  to make any change to code though it is showing correct when filtering but not showing correct results with slicer and data in table. Kindly help it would be appreciated.

Thanks

[v-yiruan-msft]

Hi @Junaid11 ,

The two fields(Period and DollarSales) in the table visual below are fields from which fact table? Are they from the fields in table PuigWeeklySkuByStore_data2? If so, does the table PuigWeeklySkuByStore_data2 and the newly created calculated table "WTD/MTD/QTD/YTD" create a relationship based on the date field? If not, could you provide some sample data from the tables involved(exclude sensitive data)? It is better if you can provide a simplified pbix file without any sensitive info. Thank you.

In addition, you can refer the following links to get the WTD calculation.

Week to Date Calculation in Power BI with DAX

Power BI — WTD Questions— Time Intelligence 4–5

WTD Calculation (See the post from mno on Jan' 21)

Best Regards

[amitchandak]

@Junaid11 , I usually use a measure like this for WTD with week rank and week day

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))

,

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

WeekDay = WEEKDAY([Date],2) //monday

WTD based on today

This Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1
var _end =today()
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter

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...
https://www.youtube.com/watch?v=pnAesWxYgJ8