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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 51 members 1,110 guests
Please welcome our newest community members: