Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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] ) ), .......)
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!
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] ) ), .......)
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"
Can you share your formula?
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |