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.
I have a dataset which consists of these kind of records:
Username Date Value
user1 11.02.2018 4
user1 12.02.2018 6
user2 11.02.2018 3
user2 16.02.2018 7
user3 10.02.2018 8
Now I have a time slicer with "relative" option set, let's say "this month".
I need to present the average values in a table for each user (per week), but for that I also need the dates, the max and min date for calculating the DATEDIFF in weeks. However, the max and min date are always calculated "per user", it means in the case of the example above for user1 I would have min(date)=11.02.2018 and max(date)=12.02.2018
I would like to know how can I get the min and max date for the whole set, based on the values selected from slicer. E.g. if I select "this month", I would like to have for each record the min(date)=1.Feb.2018 and max(date)=28.Feb 2018 (or today, 14.Feb2018).
How can I achieve this?
Solved! Go to Solution.
@lkarolak,
What date field do you use to create the slicer? And does your table contain any other date values?
If you have a calendar table, use date field of the calendar table to create slicer, then create the following measures in your original table, you will get expected result when selecting values in the date slicer.
maxdate = CALCULATE(MAX('Date'[Date]))
mindate = CALCULATE(MIN('Date'[Date]))
Regards,
Lydia
@lkarolak,
What date field do you use to create the slicer? And does your table contain any other date values?
If you have a calendar table, use date field of the calendar table to create slicer, then create the following measures in your original table, you will get expected result when selecting values in the date slicer.
maxdate = CALCULATE(MAX('Date'[Date]))
mindate = CALCULATE(MIN('Date'[Date]))
Regards,
Lydia
Hi @lkarolak,
Forgive me if I got this wrong, but if your goal is to present average values for each user per week, I am proposing a slightly different solution.
weeknum = WEEKNUM(Table1[Date],2)
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |