Non-date visual slicer for week to date column

[Anonymous]

Hi team,

I'm looking for answer whether i can display week-to-date formula with visual filter that is not related to date

There are 2 tables : Date and UD

[Date].Date is connected to [UD].StartDate

I have Week-To-Date formula to count number of 'ID' and this is perfectly working fine with date slicer:

#Apps_WTD =

var CurrentDate = LASTDATE('Date'[Date])

var DayNumberOfWeek = WEEKDAY(LASTDATE('Date'[Date]), 3)

RETURN

CALCULATE(

COUNT(UD[Id]),

DATESBETWEEN( 'Date'[Date],

DATEADD(CurrentDate, -1*DayNumberOfWeek, DAY),

CurrentDate)

)

Then, I also need to provide last week-to-date data for each 'Status'. 'Status' column is available in UD table and this is where I can't find the answer. Table below is what I'm expecting

Status	Last Week To Date - Count of Id
Approved	A
Declined	B
Referred	C

Instead this is what I got when 'Week to date' formula is dragged to the table visual, it just returned blank

Status	Last Week To Date - Count of Id
blank	blank

This can be achieved for MTD and YTD with their built-in function and I'm not sure how to achieve the same for WTD. Can anyone shed some light please ?

[Anonymous]

@amitchandak  thank you. i used the WTD method in your blog, it gives the correct result when using date slicer in table but incorrect if the slicer is status. I have attached link to PBIX file where WTD 2 formula where it is based on your blog.

@v-henryk-mstf  yes, the DAX formula I wrote will return blank if status slicer is used, but correct for date slicer. Attached is the PBIX file with WTD 1 and WTD 2 and both DAX formulas are not able to return value WTD with Status as slicer.

Link to PBIX file : https://drive.google.com/file/d/1NuaMgkVqmj0v8_aAFF1IQkQMfyYwj1at/view?usp=sharing 

Any idea what I did wrong ?

[v-henryk-mstf]

Hi @Anonymous ,

According to the information you give, the result returned by the DAX formula you wrote is blank. I checked the errors. According to the DATEADD(CurrentDate, - 1 * DayNumberOfWeek, Day) filter conditions, if you want to get dates that are one day before the dates in the current context, you might as well change it to DATEADD(CurrentDate, - 1, day). If the result is still problems, you may as well provide two more tables with more detailed data and information. I can help you with further tests.

Best Regards,
Henry

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , refer my blog on week on week and WTD vs last WTD , if that can help

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