Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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:
If Date period end date got selected as “03/01/2021”.
output to show number of user should be as like below
Last 30 days | 30 to 60 Days | Beyond 60 days |
4 | 3 | 6 |
If another date is selected then the calculation should happen from that selected date.
Thanks in advance.!
Solved! Go to Solution.
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))
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))
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
16 | |
2 | |
2 | |
1 | |
1 |