Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Bhavyabujji
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
Bhavyabujji
Frequent Visitor

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.

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.

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

 

 
 

ANy update

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.

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.

Any update please we need this on high prioirty 

Any updates please

Hi @Bhavyabujji,

Sorry, I could not have access to your file from your link:

1.PNG

Could you please upload your file to OneDrive or Dropbox and send the link here 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.

i have uploaded in OneDrive site only, can you please share the mail id so that i can share the file to your mail id

Hi @Bhavyabujji,

You could upload your report to your OneDrive and send the link to me via private message.

 

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.
v-danhe-msft
Employee
Employee

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.