Calculating max date in a measure from the relative date slicer

[lkarolak]

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?

[v-yuezhe-msft · Accepted 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

[CristinaLia]

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.

1. Build a calculated column for the the week using the formula below
2. Build a matrix visual dragging: weeknum under rows, user under column and value under Values.
3. the 'Value' field, right click and chnage it to average, and the result should be as the image attached

weeknum = WEEKNUM(Table1[Date],2)