Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a Date Table dimension (created in DAX) and a fact Sales table where there is a connection with Dates[Date] and Sales[Date].
I need to get the max Year on the date filter and show the previous year net value.
Probably there are other specific functions to do these but I need the calculation to be done in DAX.
If the Max Year of the date slicer is 2024, when I use the following calculation it works:
Net Value = Calculate(
SUM(Sales[NetValue]),
Dates[Year] = 2023
)
BUT if I do this it doesn't work:
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),
Dates[Year] = previousYear
)
Can anyone help me?
Solved! Go to Solution.
pls try this
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),FILTER(All(Dates),
Dates[Year] = previousYear)
)
or ------
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
Dates[Year] = previousYear)
)
if you write it like this what do you get ?
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
previousYear
If I use that measure in a chart bar where the x-axis is the year, it returns properly the previous year for ech year
pls try this
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),FILTER(All(Dates),
Dates[Year] = previousYear)
)
or ------
Net Value =
VAR previousYear = MAX(Dates[Year]) -1
RETURN
Calculate(
SUM(Sales[NetValue]),FILTER(ALLSELECTED(Dates),
Dates[Year] = previousYear)
)
I get 2023, which is the expected value since in the example the max is 2024