Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MM_NL
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
popov
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

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

MM_NL
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"

Can you share your formula?

MM_NL
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.