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

Dynamically change the data count in Power BI visual based on Date selection in slicer

Hi Everyone,

I have a below explained use-case to implement, but not able to think about how we can implement it in Power BI. So, please help me out if you have any suggestions on implementing this below logic.

 

I have a Source data as like below.


Data Set.PNG
Use case is based on date selection by date slicer
, dynamically count of users to be displayed for Last 30 Days, 30 to 60 Days and Beyond  60 days.

Date Slicer is like this:

sxs58230_0-1613745318273.jpeg

 

If Date period end date got selected as “03/01/2021”.

output to show number of user should be as like below

 

Last 30 days30 to 60 DaysBeyond  60 days
436



If another date is selected then the calculation should happen from that selected date. 

Thanks in advance.!

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

Don't forget that December has 31 days. Also note that "last 30 days" is ambiguous. Does it include the max date or not?  Below formula actually covers 31 days, not 30. 

 

 

 

 

Last 30 days := COUNTROWS(filter('Sample','Sample'[Login Date] in DATESINPERIOD(Dates[Date],max(Dates[Date]),-30,DAY)))

30 to 60 days := COUNTROWS(filter('Sample','Sample'[Login Date] in DATESINPERIOD(Dates[Date],max(Dates[Date])-31,-30,DAY)))

Beyond 60 days := var d = max(Dates[Date])
return COUNTROWS(filter('Sample','Sample'[Login Date]<=d && DATEDIFF('Sample'[Login Date],d,DAY)>60))

 

 

lbendlin_0-1613863666922.png

 

 

View solution in original post

3 REPLIES 3
lbendlin
Super User III
Super User III

Don't forget that December has 31 days. Also note that "last 30 days" is ambiguous. Does it include the max date or not?  Below formula actually covers 31 days, not 30. 

 

 

 

 

Last 30 days := COUNTROWS(filter('Sample','Sample'[Login Date] in DATESINPERIOD(Dates[Date],max(Dates[Date]),-30,DAY)))

30 to 60 days := COUNTROWS(filter('Sample','Sample'[Login Date] in DATESINPERIOD(Dates[Date],max(Dates[Date])-31,-30,DAY)))

Beyond 60 days := var d = max(Dates[Date])
return COUNTROWS(filter('Sample','Sample'[Login Date]<=d && DATEDIFF('Sample'[Login Date],d,DAY)>60))

 

 

lbendlin_0-1613863666922.png

 

 

View solution in original post

lbendlin
Super User III
Super User III

1. Why do you need a date range slicer? A single date picker seems more appropriate

2. Please provide sample data in usable format (not as a picture) .

Hi @lbendlin thanks for your response.

 

Below is the required details:

 

1. The Date Slicer is the requirement so it is required as is.

2. Sample Data:

 

User     Login date
USER1   01/10/2020
USER2   02/10/2020
USER3   03/10/2020
USER4   04/10/2020
USER5   01/11/2020
USER1   02/11/2020
USER2   03/11/2020
USER3   04/11/2020
USER4   05/11/2020
USER1   10/12/2020
USER2   11/12/2020
USER3   12/12/2020
USER2   03/01/2021
USER3   04/01/2021

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.