Forum Discussion
Using DATEDIFF in measure
Hello! I've spent some time attempting to use DATEDIFF in a dax measure but failed to do so. Here's the basics; I have two tables, "Sales" and "Stores". I am trying to display sales over time within ...
Anonymous
Sales Filter = VAR CurrentYear_ = YEAR ( MAX ( DateT[Date] ) ) // This could also be: VAR CurrentYear_ = MAX(DateT[Year]) RETURN CALCULATE ( SUM ( Sales[sale] ), FILTER ( Sales, YEAR ( RELATED ( Stores[OpenedDate] ) ) <> currentYear_ ) )Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Anonymous
Sales Filter =
VAR CurrentYear_ =
YEAR ( MAX ( DateT[Date] ) ) // This could also be: VAR CurrentYear_ = MAX(DateT[Year])
RETURN
CALCULATE (
SUM ( Sales[sale] ),
FILTER ( Sales, YEAR ( RELATED ( Stores[OpenedDate] ) ) <> currentYear_ )
)Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Thank you. The Dax you provided works as a intented. I tried adding an additional filter in the FILTER using LOOKUPVALUE instead of RELATE to connect a 'Refit' table that also has an 'OpenedDate' column in an attempt to filter stores that have gone thru a refit as well. This time however the code nolonger seems to work:
Sales Filter =
VAR CurrentYear_ =
YEAR ( MAX ( DateT[Date] ) ) // This could also be: VAR CurrentYear_ = MAX(DateT[Year])
RETURN
CALCULATE (
SUM ( Sales[sale] ),
FILTER ( Sales,
YEAR ( RELATED ( Stores[OpenedDate] ) ) <> currentYear_ &&
YEAR ( LOOKUPVALUE( Refit[OpenedDate], Refit[StoreID], Sales[StoreID] ) <> currentYear_
)
)