Hello all, I am looking at creating a date filter with the following criteria. Appreciate your help! Thanks in advance
How can one create a date slicer that shows only one week at a time.
If today is Monday,Aug23 then the slicer should show previous week(M-F) 16th-20th and today Monday23rd.
If today is Tuesday,Aug 24 then the slicer should show Monday,23rd, Tuesday 24.
If today is Friday,27thAug, then the slicer should show M-F of the current week 23-27
Hi Vsb79,
Have you tried using Power Query? you could create a Date Slicer column that meets your needs.
I will have a play and see what I come up with.
Hi @Vsb79,
@KimC is correct you can solve that with Power Query, please look into this topic on creating a dynamic date range slicer and see if that gets you there.
Should you require further assistance, provide a mock-up PBIX file along with the data.
I hope this is helpful
Hi @Vsb79, did the response provided by the other users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
So, this was helpful. In addition to this, I need to show a date slicer(Start of the week is Monday) that shows the following:
Thank you
Hi @Vsb79,
You can customize the Dynamic date range slicer to meet these criteria OR solve it with DAX
Again if you need further assistance provide a work in progress PBIX (with a data file) and a mock up of the expected result.
Hello @Vsb79, good to see that you are having progress with your inquiry. Did the response from @Melissa help you solve your inquiry?
If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
Hi @Vsb79, we’ve noticed that no response has been received from you since Sep 1. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
I am trying to understand and implement the code to my needs. I have a need to implement this either in power query or dax. Basically the date table I have should be getting one weeks data similar to having a condition on date table if we do in sql like below:
Select distinct date from [tablename] where
date>= dateadd(day, -((datepart(dw, getdate())+5)%7)-1, convert(date,getdate()))
and date<=Dateadd(day,0, convert(date,getdate()).
I feel that current week to date is what gives the above result. Is my understanding correct? I still am learning to understand M code and so not sure if my understanding is right!
Thank you for your support!
Hi @Vsb79,
Add this to your Dates table query. It requires your date column to be called “Date” as second argument in the List.Contains function, amend to your needs.
AddRollingWeek = Table.AddColumn(Source, "Is Rolling Week", each
List.Contains(
let
TodaysDate = Date.From(DateTimeZone.FixedUtcNow()),
GetDates =
if Date.From( Date.StartOfWeek( TodaysDate, Day.Monday)) = TodaysDate
then List.Dates( Date.From(Date.StartOfWeek(Date.AddWeeks(TodaysDate, -1), Day.Monday)), 7, Duration.From(1))
else List.Dates( Date.From(Date.StartOfWeek(TodaysDate, Day.Monday)), Number.From( TodaysDate - Date.From(Date.StartOfWeek(TodaysDate, Day.Monday)))+1, Duration.From(1))
in
GetDates, [Date] )
)
.Returns this result.
I hope this is helpful.
Hi @Vsb79, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Vsb79, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.
