cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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] ) ), .......)

View solution in original post

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!

View solution in original post

4 REPLIES 4
Highlighted
Resolver III
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] ) ), .......)

View solution in original post

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

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!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

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)