topic Re: CALCULATE() with measure as a date filter in DAX Commands and Tips

CALCULATE() with measure as a date filter

Anonymous — Thu, 12 Sep 2019 15:54:36 GMT

Hi all,

I'm using a measure to find the last date of a row in table (table1) already filtered by a date segment. Looks like this:

Last _date =
CALCULATE(LASTDATE(Table1[date]); #find the last date available in table1
DATESINPERIOD('CALENDAR'[Date];LASTDATE('CALENDAR'[Date]);-7;DAY)) #define the range of dates

Until here I know it's working properly because when I visualize the data I have the last date for all my items.

Item1 | Last_date_1

Item2 | Last_date_2

....

Now I want to add a an other measure to this visual. This is were I'm lost.

I would like to use my last_date measure as a date filter in a new CALCULATE() function.

Something like this but it's not working saying that a CALCULATE function has been used in a filter..

Volume_measure=CALCULATE(SUM(Table1[Volume]);Table1[date]=[Last_date])

Any idea, trick?

Thank you

Re: CALCULATE() with measure as a date filter

Anonymous — Thu, 12 Sep 2019 16:36:43 GMT

Problem solved with VAR

Re: CALCULATE() with measure as a date filter

Anonymous — Tue, 17 Sep 2019 07:50:39 GMT

As requested by few people, the code should look like this

New_measure =

VAR

variable=[last_date] #last_date is a measure

RETURN

CALCULATE(AVERAGE(Table1[volume];Table1[date]=variable)

last_date=

CALCULATE(LASTDATE(Table1[date]); #find the last date available in table1
DATESINPERIOD('CALENDAR'[Date];LASTDATE('CALENDAR'[Date]);-7;DAY)) #define the range of dates

SingSong