Forum Discussion
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)
Hello, MM_NL
The Syntax of DATEADD function isDATEADD(<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] ) ), .......)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
Hello, MM_NL
The Syntax of DATEADD function isDATEADD(<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] ) ), .......)