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

## Max slicer date as input for dateadd

I am trying to make a measure wich always calculates an average over the past 5 months, based on the max selected date in a slicer

The problem i have is that the dateadd function doenst work with a calculation like, does someone have a solution.

Parts of the Function at the moment (there are some more variables but they work fine, problem is the bold parts):

var periode = DATESBETWEEN(V_PeriodeMaand[EersteDagVdMaand],  DATEADD(max(V_PeriodeMaand[EersteDagVdMaand]), -4, MONTH) ,  [Laatstedatumselectie]

RETURN
CALCULATE((Verzuimdagen/Beschikbaredagen), FILTER(V_PeriodeMaand, V_PeriodeMaand[EersteDagVdMaand] in(periode)))

In case the language causes any problems, in essence this is the var:

DATESBETWEEN(Period,  DATEADD(max(Period), -4, MONTH) ,  max(Period)

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver III

Hello, @MM_NL
The Syntax of DATEADD function is

DATEADD(<dates>,<number_of_intervals>,<interval>)
<dates> is column, not scalar value. MAX
(Period) isn't work. You must set column, if you need only one date, you can use construction something like this:
DATEADD ( FILTER ( Table[Period], Table[Period] = MAX (Table[Period] ) ), .......)

Highlighted
Frequent Visitor

I solved it, by replacing the table input in the filter expression by lastdate() on the date column

Old:

DATEADD(FILTER(Period, Period[Datecolumn] = max(Period[Datecolumn]))

New

DATEADD(FILTER(LASTDATE(Period[Datecolumn]), Period[Datecolumn] = max(Period[Datecolumn]))

this way the filter expression accepted a column input instead of whole table

Thank you Popov!

4 REPLIES 4
Highlighted
Resolver III

Hello, @MM_NL
The Syntax of DATEADD function is

DATEADD(<dates>,<number_of_intervals>,<interval>)
<dates> is column, not scalar value. MAX
(Period) isn't work. You must set column, if you need only one date, you can use construction something like this:
DATEADD ( FILTER ( Table[Period], Table[Period] = MAX (Table[Period] ) ), .......)

Highlighted
Frequent Visitor

I tried this and the error i get now is:

"A table expression containing more than one column was specified in the call to function 'dateadd'. this is not supported"

Highlighted
Resolver III

Can you share your formula?

Highlighted
Frequent Visitor

I solved it, by replacing the table input in the filter expression by lastdate() on the date column

Old:

DATEADD(FILTER(Period, Period[Datecolumn] = max(Period[Datecolumn]))

New

DATEADD(FILTER(LASTDATE(Period[Datecolumn]), Period[Datecolumn] = max(Period[Datecolumn]))

this way the filter expression accepted a column input instead of whole table

Thank you Popov!

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors
Users online (588)