Max slicer date as input for dateadd

[MM_NL]

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)

[popov · Accepted solution]

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] ) ), .......)

[popov · Accepted solution]

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] ) ), .......)

[MM_NL]

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"

[popov]

Can you share your formula?

[MM_NL · Accepted solution]

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!