cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bhavyabujji Frequent Visitor
Frequent Visitor

get the data from last week Monday to till today (Range)

Hi Team,

 

I am very new to Power BI and i have below requirement, can you please help me with this.

 

i have one table with Date, Sitename, region, sub region etc., fields.

 

In Power BI Report view i have one Sitename slicer and one date slicer type as relative, and few table visuals to show the respective data.

1. When ever user selects the Sitename from slicer, we need to get the max date data and the range to get this max date is previous week monday to till today.

2. Once we have the max date data from the range mentioned in first point, now if the user changes the dates range using date slicer (not changing the Site name slicer) we need to get the respective data of the selected range in date slicer.

 

Hope this is clear and let me know for any queries, please help me to get this done.

 

Thank you in advance. 

12 REPLIES 12
v-danhe-msft Super Contributor
Super Contributor

Re: get the data from last week Monday to till today (Range)

Hi @Bhavyabujji,

Could you please share some sample data to have a test?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

HI Daniel,

 

unable to add sample data and power bi file FYR.

 

Note - when ever we are loading the report we are hiding the data using Hide measure and you can see the formula.

Now, In Power BI Report view i have one Sitename slicer and one date slicer type as relative, and few table visuals to show the respective data.

1. When ever user selects the Sitename from slicer, we need to get the max date data and the range to get this max date is previous week monday to till today i am able to get this information using this measure  NOW()-7-WEEKDAY(NOW(),3) 

2. Once we have the max date data from the range mentioned in first point, now if the user changes the dates range using date slicer (not changing the Site name slicer) we need to get the respective data of the selected range in date slicer.

 

Let me know for any other information.

 

 Could please let me know how to add sample excel and power bi file to this conversion as i am not able to attach it over here.

v-danhe-msft Super Contributor
Super Contributor

Re: get the data from last week Monday to till today (Range)

Hi @Bhavyabujji,

You could upload your report to your OneDrive or Dropbox and send the link here, could you please post a desired result if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

Hi Daniel,

 

Based on  site name slicer selection, i am flitering the data of last week Monday to till today, if no data it should be NA and i am using below calucluated columns and doing visual level filters using RoundDate.

 

PreviousweekMonday = NOW()-7-WEEKDAY(NOW(),3)
 
LastCustomWeek = FORMAT(CALCULATE(MAX('Table3 (2)'[Date]),FILTER('Table3 (2)','Table3 (2)'[Date] > ('Table3 (2)'[PreviousweekMonday]-1)
&& 'Table3 (2)'[Date] <= NOW() && 'Table3 (2)'[Site]=SELECTEDVALUE('Table3 (2)'[Site]))),"dd/mm/yyyy")
 
RoundDate = IF(Format('Table3 (2)'[Date],"dd/mm/yyyy")=('Table3 (2)'[LastCustomWeek]),1,2).
 
In the same report once the above requirement got executed, using Betweeen Date Slicer i want to change the dates range , so now i want to unfilter the above filtered data and show the data between the selected range. attached sample report and data.
 
sample data.png

 

site slicer.png

 

 
 
Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

ANy update

v-danhe-msft Super Contributor
Super Contributor

Re: get the data from last week Monday to till today (Range)

Hi @Bhavyabujji,

From your description, it seemed if you have selected one item  from the Sitename slicer, it do will effect the range data slicer, could you have tried change the interaction in your report?

Reference:https://docs.microsoft.com/en-us/power-bi/service-reports-visual-interactions

I still hope you could offer me a sample pbix if possible.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

Hi Daniel,

 

I had added the files in below link https://wipro365-my.sharepoint.com/personal/bpasu_wipro_com/_layouts/15/onedrive.aspx?id=%2Fpersonal...

 

with below formulas i am able to get data 

PreviousweekMonday = NOW()-7-WEEKDAY(NOW(),3)
 
LastCustomWeek = FORMAT(CALCULATE(MAX('Table3 (2)'[Date]),FILTER('Table3 (2)','Table3 (2)'[Date] > ('Table3 (2)'[PreviousweekMonday]-1)
&& 'Table3 (2)'[Date] <= NOW() && 'Table3 (2)'[Site]=SELECTEDVALUE('Table3 (2)'[Site]))),"dd/mm/yyyy")
 
RoundDate = IF(Format('Table3 (2)'[Date],"dd/mm/yyyy")=('Table3 (2)'[LastCustomWeek]),1,2).
 
In the same report once the above requirement got executed, using Betweeen Date Slicer i want to change the dates range , i am able to get the dates and data between the selected date ranges also
DateMeasure = FORMAT(CONCATENATEX(VALUES('Table3 (2)'[Date]),
CALCULATE(FORMAT(SELECTEDVALUE('Table3 (2)'[Date]),"dd/mm/yyyy")),
","),"dd/mm/yyyy")
 
so now only showing the filtered in visuals is pending either by unfilter the above (first) filtered data and show the data between the selected range or adjust the Rounddate parameter, attached sample report and data in the URL.
 
Please help me on this or suggest me.
Highlighted
Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

Any update please we need this on high prioirty 

Bhavyabujji Frequent Visitor
Frequent Visitor

Re: get the data from last week Monday to till today (Range)

Any updates please

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 362 members 3,977 guests
Please welcome our newest community members: