Need help in Date - Please HELP!!!

[Anonymous]

Im having a change management dashboard which I have created where in the data that needs to be shown to the management on a week on week manner.

So the team basically sends this out every Week and the data is extracted from Monday to Sunday irrespective of the Month. I have a Start Date which is extracted from ServiceNow. The date in ServiceNow is as per PST and want it to stay that way in PowerBi. The same is for the End Date as well.

Here is the situation 

This the Filter I have kept

So when I select the Start Date as THIS WEEK and End Date as THIS WEEK in the Filter it should take up 7th June to 13th June 2021. If I select this tomorrow as THIS WEEK still it should show as 7th June to 13th June. and the Display should be 7th June to 13th June (Which is MONDAY TO SUNDAY)

If the team runs this report Next week with Filter (Start Date and End Date) as THIS WEEK then both the display and report should show (14th JUNE to 20th JUNE) MONDAY to SUNDAY.

Attaching screenshot of the data as well.

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can now roughly understand your requirement, you can my steps:

1. Create a what-if parameter and create a slicer based on it:

2. Create a measure like this:

Flag1 =

var _weeknum=WEEKNUM(TODAY(),2)-[Last N week Value]

return

IF(WEEKNUM(MAX('Table'[Date]),2)=_weeknum&&YEAR(MAX('Table'[Date]))=YEAR(TODAY()),1,0)

3. Go to the table chart and change the visual filter like this:

And you can get what you want.

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @Anonymous 

According to your description, now I can roughly understand your requirement, I think you can achieve this using a measure and the visual filter, you can try my steps:

1. Create a calendar table for the date slicer:

Date = CALENDAR(DATE(2021,1,1),DATE(2021,12,31))

2. Create a measure as the flag:

Flag =

var _startweek=WEEKNUM(MIN('Date'[Date]),2)

var _endweek=WEEKNUM(MAX('Date'[Date]),2)

return

IF(WEEKNUM(MAX('Table'[Date]),2)<=_endweek&&WEEKNUM(MAX('Table'[Date]),2)>=_startweek&&YEAR(MAX('Table'[Date]))=YEAR(MAX('Date'[Date])),1,0)

3. Then you can go to create a date between slicer and a table chart with the visual filter like this:

And you can get what you want.

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Firstly thanks so much for checking my query out and I really Appreciate you helping me out here!!

Though, I tried this out it doesnt kind of actually fit with what Im expecting or can say its kinda close but what I have decided to do is explain this again with more clarity so that I can get the solution. I am attaching the sample data for your reference.

Source :- This data originally is fetched from ServiceNow through Get call and fetches data real time. For sample data purpose I have put it on excel.

Visual Slicer :- I have selected it a slicer which has Start Date and End Date and formatted it as Relative Date.

There is a graph and a Card which I have added.

Below is the requirement :-

Whenever I select filter as for Start Date as LAST 1 WEEK & End Date as Last 1 week then the graph and Card values and visual would Change. The criteria that the management has requested is that

1. When we select Start Date as LAST 1 Week then it should take from Monday to Sunday of the Last week. So for eg :- Today is 12th June 2021 and someone select Last 1 Week and End date as Last 1 Week it should take from 31st May to 6th June 2021 ( Monday to Sunday ) instead of looking at last 7 days. Basically they want to see Monday to Sunday data. If I select This week as Start Date and End Date then it should select (7th June to 13th June). Hope this makes sense?

Let me know if its still unclear. 

[Msta]

I have read your post twice and I don't get what is your problem. What kind of help do you need?

[Anonymous]

Hi,

Please let me know if my issue was difficult to understand.

I want the start date and end date when selected as This week or Last week it should show data from Monday to Sunday of every week. Can this be done?

The visual beside the title which shows date should reflect as and when I change the Start date and End date. So eg :- if I select Start date as Last week and End Date as Last week then the data should be picked up from Monday to Sunday which is (31st May to 6th June) Monday to Sunday and same should be shown in the display.

Let me know if this makes sense.